package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.poi.ss.usermodel.Workbook;

import services.PollService;
import services.ValidationService;
import utils.Utils;
import business.Button;
import business.Campaign;
import business.City;
import business.Company;
import business.CompanyCampaign;
import business.CompanyPoll;
import business.Country;
import business.CoverDefinition;
import business.FormMovement;
import business.FormStatus;
import business.Indicator;
import business.IndustrialMP;
import business.IndustrialProduct;
import business.LegalForm;
import business.Local;
import business.LocalMenu;
import business.LocalType;
import business.MiningItem;
import business.NameType;
import business.Permission;
import business.Poll;
import business.PollDefinition;
import business.PollStatus;
import business.PollValue;
import business.Province;
import business.Role;
import business.SendValidationResult;
import business.State;
import business.User;
import business.UserCompanyCampaign;
import business.Validation;
import business.ValidationError;
import business.ValidationReason;
import business.ValidationType;
import db.BoundParam;
import db.Conexion;
import exceptions.ExDBException;

public class ObjectDao {
	
	public static final String OR_OPERATOR = "OR";
	public static final String AND_OPERATOR = "AND";
	private Conexion conn;	
	private Properties props = new Utils().getPropertiesByResource("resource/configuration.properties");
	
	public ObjectDao() {
		super();
		conn = new Conexion();
	}

	private String getMainSchema()
	{
		String mainSchema = props.getProperty("schema.main.name");
		return mainSchema;
		//TODO Mostrar excepcion o algo cuando no este la entrada en el properties
	}
	
	private String getSecondarySchema()
	{
		String secondarySchema = props.getProperty("schema.secondary.name");
		return secondarySchema;
		//TODO Mostrar excepcion o algo cuando no este la entrada en el properties
	}
	
	private int getDataTypeValue(int filterDataType)
	{
		if (filterDataType == Filter.BOOLEAN_FILTER)
		{
			return java.sql.Types.BOOLEAN;
		}
		else if (filterDataType == Filter.NUMERIC_FILTER)
		{
			return java.sql.Types.NUMERIC;
		}
		else if (filterDataType == Filter.STRING_FILTER)
		{
			return java.sql.Types.VARCHAR;
		}
		else
		{
			return java.sql.Types.VARCHAR;
		}
	}

	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	private String getProduct(Long idLocal)
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, idLocal,java.sql.Types.NUMERIC));
		parameters.add(new BoundParam(2, idLocal,java.sql.Types.NUMERIC));
		parameters.add(new BoundParam(3, idLocal,java.sql.Types.NUMERIC));
		
		String sql = "select (CASE WHEN id_actividad in ( select id_actividad from "+ getSecondarySchema() +".local where id_actividad between '05000' and '08999') "
				+ "THEN ( "
				+ "select cp.descripcion from "+ getSecondarySchema() +".clanae_productos cp "
				+ "inner join "+ getSecondarySchema() +".empresa_has_actividad eha  on eha.ID_MAT = cp.cod_sene "
				+ "inner join "+ getSecondarySchema() +".local l on l.id_empresa = eha.id_empresa "
				+ "where l.id_local  = ? and cp.codclapr = '05100' and rownum <= 1 ) "
				+ "ELSE  ( "
				+ "select cp.descripcion from "+ getSecondarySchema() +".clanae_productos cp                      "
				+ "inner join "+ getSecondarySchema() +".empresa_has_actividad eha  on eha.ID_PROD = cp.cod_sene "
				+ "inner join "+ getSecondarySchema() +".local l on l.id_empresa = eha.id_empresa "
				+ "where l.id_local  = ? and rownum <= 1) "
				+ "END ) from "+ getSecondarySchema() +".local se where se.id_local = ? ";
		ResultSet rs;
		Conexion connection = new Conexion();
		try {
			connection.abrirConexion();
			String result = null;
			rs = connection.getPreparedStatementResultSet(sql,parameters);
			if(rs.next())
			{
				result = rs.getString(1);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally
		{
			connection.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	private String getRawMaterial(Long idLocal)
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, idLocal,java.sql.Types.NUMERIC));
		parameters.add(new BoundParam(2, idLocal,java.sql.Types.NUMERIC));
		parameters.add(new BoundParam(3, idLocal,java.sql.Types.NUMERIC));
		
		String sql = "select (CASE WHEN id_actividad in ( select id_actividad from "+ getSecondarySchema() +".local where id_actividad between '05000' and '08999') "
				+ "THEN ( "
				+ "select cm.descripcion from "+ getSecondarySchema() +".clanae_materiaprima cm "
				+ "inner join "+ getSecondarySchema() +".empresa_has_actividad eha  on eha.ID_MAT = cm.cod_sene "
				+ "inner join "+ getSecondarySchema() +".local l on l.id_empresa = eha.id_empresa "
				+ "where l.id_local  = ? and cm.codcla = '05100' and rownum <= 1 ) "
				+ "ELSE  ( "
				+ "select cm.descripcion from "+ getSecondarySchema() +".clanae_materiaprima cm "
				+ "inner join "+ getSecondarySchema() +".empresa_has_actividad eha  on eha.ID_MAT = cm.cod_sene "
				+ "inner join "+ getSecondarySchema() +".local l on l.id_empresa = eha.id_empresa "
				+ "where l.id_local  = ? and rownum <= 1) "
				+ "END ) from "+ getSecondarySchema() +".local se where se.id_local = ? ";
		ResultSet rs;
		Conexion connection = new Conexion();
		try {
			connection.abrirConexion();
			String result = null;
			rs = connection.getPreparedStatementResultSet(sql,parameters);
			if(rs.next())
			{
				result = rs.getString(1);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally
		{
			connection.cerrarConexion();
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	private String getMainActivity(Long idLocal)
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, idLocal,java.sql.Types.NUMERIC));
		
		String sql = "select NOMBRE_ACTIVIDAD from "+ getSecondarySchema() +".clanae_completa cc "
				+ "inner join "+ getSecondarySchema() +".empresa_has_actividad eha on  cc.id = eha.actividad_id_actividad "
				+ "inner join "+ getSecondarySchema() +".local sl on sl.id_empresa = eha.id_empresa "
				+ "where sl.id_local = ? and eha.actividad_id_actividad = sl.id_actividad ";
		ResultSet rs;
		Conexion connection = new Conexion();
		try {
			connection.abrirConexion();
			String result = null;
			rs = connection.getPreparedStatementResultSet(sql,parameters);
			if(rs.next())
			{
				result = rs.getString(1);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally
		{
			connection.cerrarConexion();
		}
	}
	
	//____________________________________________________________________________
	//Creado Por la gente de Indec
	//Una Pinturita! =D
	public List<LocalMenu> getLocalsOnly(Long idEmpCamp)
	
	{
		List<LocalMenu> localListOnly = new ArrayList<LocalMenu>();
		try{
				LocalMenu locales = null;
				CompanyPoll anexo = null;
				Boolean esNuevoLocal = true;
				conn.abrirConexion();
				List<BoundParam> parameters = new ArrayList<BoundParam>();
				StringBuilder sql = new StringBuilder();
				
				sql.append("select sec.id_emp_camp,sef.idlocal,sef.clanaerel,see.id_encuesta,senc.NOMBRE_ENCUESTA,sl.id_actividad, sp.provincia,sl.nro_local,see.id_estado_formulario "); 
				sql.append("from "+ getSecondarySchema() +".local sl ");
				sql.append("left join "+ getSecondarySchema() +".provincia sp on sp.id_provincia = sl.id_provincia ");
				sql.append("left join "+ getMainSchema() +".encuestas_empresas see on see.id_local = sl.id_local ");
			    sql.append("left join "+ getMainSchema() +".empresas_campanas sec on sec.id_emp_camp = see.id_emp_camp ");
			    sql.append("left join "+ getMainSchema() +".encuestas senc on senc.id_encuesta = see.id_encuesta ");
			    sql.append("left join " +getSecondarySchema() +".formularios_empresa sef on sl.id_local = sef.idlocal ");
			    sql.append("where see.id_emp_camp = "+ idEmpCamp + " ");
			    sql.append("group by sec.id_emp_camp,sef.idlocal,sef.clanaerel,see.id_encuesta,senc.NOMBRE_ENCUESTA,sl.id_actividad, sp.provincia,sl.nro_local,see.id_estado_formulario "); 
			    sql.append("order by sl.id_actividad desc , sl.nro_local asc");
				
					//System.out.println(sql);
				ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			    
			    while (rs.next()) {
			    	locales = new LocalMenu();
			    	locales.setLocal(new Local());
			    	locales.setAnnexes(new ArrayList<CompanyPoll>());
			    	anexo = null;
			    	
			    	locales.getLocal().setClanae(rs.getString("ID_ACTIVIDAD"));
			    	locales.getLocal().setLocalNumber(rs.getLong("NRO_LOCAL"));
			    	locales.getLocal().setId(rs.getLong("IDLOCAL"));
			    	
			    	locales.getLocal().setProvince(new Province());
			    	locales.getLocal().getProvince().setProvince(rs.getString("PROVINCIA"));
			    	
			    	if (rs.getLong("ID_ENCUESTA") == 2){
			    	
			    	locales.setLocalPoll(new CompanyPoll());
			    	locales.getLocalPoll().setPoll(new Poll());
			    	locales.getLocalPoll().getPoll().setName(rs.getString("NOMBRE_ENCUESTA"));
			    	locales.getLocalPoll().getPoll().setId(rs.getLong("ID_ENCUESTA"));
			    	
			    	
			    	locales.getLocalPoll().setFormStatus(new FormStatus());
			    	locales.getLocalPoll().getFormStatus().setId(rs.getLong("ID_ESTADO_FORMULARIO"));
			    	}
			    	else if (rs.getLong("ID_ENCUESTA") == 34){
				    	
				    	locales.setRegionalizedLocalPoll(new CompanyPoll());
				    	locales.getRegionalizedLocalPoll().setPoll(new Poll());
				    	locales.getRegionalizedLocalPoll().getPoll().setName(rs.getString("NOMBRE_ENCUESTA"));
				    	locales.getRegionalizedLocalPoll().getPoll().setId(rs.getLong("ID_ENCUESTA"));
				    	
				    	locales.getRegionalizedLocalPoll().setFormStatus(new FormStatus());
				    	locales.getRegionalizedLocalPoll().getFormStatus().setId(rs.getLong("ID_ESTADO_FORMULARIO"));
				    }
			    	else{
			    		anexo = new CompanyPoll();
			    		anexo.setPoll(new Poll());
			    		anexo.getPoll().setId(rs.getLong("ID_ENCUESTA"));
			    		anexo.getPoll().setName(rs.getString("NOMBRE_ENCUESTA"));
			    		
			    		anexo.setFormStatus(new FormStatus());
			    		anexo.getFormStatus().setId(rs.getLong("ID_ESTADO_FORMULARIO"));	    	
			    	}
			    	
			    	if (!localListOnly.isEmpty())
			    	{
			    		esNuevoLocal = true;
			    		for (LocalMenu local: localListOnly)
			    		{
			    			if (locales.getLocal().getId().equals(local.getLocal().getId()))
			    			{
			    				if (anexo != null)
			    				{
			    					local.getAnnexes().add(anexo);
			    				}
			    				if (locales.getLocalPoll() != null)
			    				{
			    					local.setLocalPoll(locales.getLocalPoll());
			    				}
			    				if (locales.getRegionalizedLocalPoll() != null)
			    				{
			    					local.setRegionalizedLocalPoll(locales.getRegionalizedLocalPoll());
			    				}
			    				esNuevoLocal = false;
			    				break;
			    			}
			    		}
			    		if (esNuevoLocal == true)
			    		{
			    			localListOnly.add(locales);
			    		}
			    	}
			    	else
			    	{
			    		localListOnly.add(locales);
			    	}
			    
			    	
			    }
				
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}return localListOnly;
		
		
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<Local> getLocals(List<Filter> filters,String operator)
	{
//		filters.add(new Filter(Filter.NUMERIC_FILTER, "EHA.ACTIVPRINCIPAL", Filter.EQUAL_OPERATOR,1,true));
		
		List<Local> localList = new ArrayList<Local>();
		try {
			Local local = null;
			Company company = null;
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			Map<Long, Company> mapCompanies = new HashMap<Long, Company>();
			
			sql.append("SELECT LOC.ID_LOCAL,LOC.NRO_LOCAL,LOC.ID_EMPRESA,LOC.ID_TIPO_NOMBRE,TN.TIPO_NOMBRE, ");
			sql.append("TIP.id_tipo_local,LOC.NOMBRE_LOCAL,LOC.CUIT_PREVISIONAL, ");
			sql.append("TIP.tipo_local,LOC.calle,LOC.nro,LOC.piso,LOC.oficina,LOC.cp,LOC.id_provincia, ");
			sql.append("PROV.provincia ,LOC.id_municipio ,MUN.municipio, LOC.id_pais, PAIS.pais,LOC.ID_DEPTO, ");
			sql.append("DEP.NOMBRE_DEPTO,LOC.inicio_local,LOC.cierre_local,LOC.CREATED_LOCAL,LOC.OBS_LOCAL, ");
			sql.append("LOC.PERSONAL,LOC.ID_ACTIVIDAD,LOC.ACTMINERIA,LOC.PAISACTIVIDAD,FE.CLANAEREL ");
			sql.append("FROM " + getSecondarySchema() + ".LOCAL2 LOC ");
			sql.append("LEFT join " + getSecondarySchema() + ".TIPO_LOCAL TIP on LOC.id_tipo_local = TIP.id_tipo_local ");
			sql.append("LEFT join " + getSecondarySchema() + ".PROVINCIA PROV on LOC.id_provincia = PROV.id_provincia ");
			sql.append("LEFT join " + getSecondarySchema() + ".MUNICIPIO MUN on LOC.id_municipio = MUN.id_municipio ");
			sql.append("LEFT join " + getSecondarySchema() + ".DEPARTAMENTO DEP on LOC.ID_DEPTO = DEP.ID_DEPTO ");
			sql.append("LEFT join " + getSecondarySchema() + ".PAIS PAIS on LOC.id_pais = PAIS.id_pais ");
			sql.append("LEFT join " + getSecondarySchema() + ".TIPO_NOMBRE TN on LOC.ID_TIPO_NOMBRE = TN.ID_TIPO_NOMBRE ");
			sql.append("LEFT join " + getSecondarySchema() + ".EMPRESA2 e2 ON  LOC.anio = e2.anio and e2.id_empresa = LOC.id_empresa ");
			sql.append("LEFT join " + getSecondarySchema() + ".FORMULARIOS_EMPRESA FE ON  FE.IDLOCAL = LOC.ID_LOCAL ");
			sql.append("LEFT join " + getMainSchema() + ".EMPRESAS_CAMPANAS ec on e2.id_empresa = ec.id_empresa ");
			sql.append("LEFT join " + getMainSchema() + ".CAMPANAS sc on sc.ID_CAMPANA = ec.ID_CAMPANA and LOC.idempanio = ec.id_empresa || sc.ano ");
			
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());
						whereUsed = true;
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));						
					}
					if(i < filters.size())
					{
						sql.append(" ");
						sql.append(operator);
						sql.append(" ");
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY LOC.ID_LOCAL ");
			
					//	System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			while (rs.next()) {
				local = new Local();
				local.setId(rs.getLong("ID_LOCAL"));
				local.setLocalNumber(rs.getLong("NRO_LOCAL"));
				NameType nameType = new NameType(rs.getLong("ID_TIPO_NOMBRE"), rs.getString("TIPO_NOMBRE"));
				local.setNameType(nameType);
				LocalType localType = new LocalType(rs.getLong("ID_TIPO_LOCAL"), rs.getString("TIPO_LOCAL"));
				local.setLocalType(localType);
				local.setNumber(rs.getString("NRO"));
				local.setStreet(rs.getString("CALLE"));
				local.setFlat(rs.getString("PISO"));
				local.setOffice(rs.getString("OFICINA"));
				local.setZipCode(rs.getString("CP"));
				Province province = new Province(rs.getString("ID_PROVINCIA"),rs.getString("PROVINCIA"));
				local.setProvince(province);
				City city = new City(rs.getString("ID_MUNICIPIO"),rs.getString("MUNICIPIO"));
				local.setCity(city);
				Country country = new Country(rs.getString("ID_PAIS"),rs.getString("PAIS"));
				local.setCountry(country);
				State state = new State(rs.getString("ID_DEPTO"),rs.getString("NOMBRE_DEPTO"));
				local.setState(state);
				local.setLocalStartDate(rs.getDate("INICIO_LOCAL"));
				local.setLocalEndDate(rs.getDate("CIERRE_LOCAL"));
				local.setLocalCreateDate(rs.getDate("CREATED_LOCAL"));
				local.setLocalActivity(rs.getString("ID_ACTIVIDAD"));
				local.setObservations(rs.getString("OBS_LOCAL"));
				local.setEmployees(rs.getLong("PERSONAL"));
				local.setMiningActivity(rs.getLong("ACTMINERIA"));
				local.setActivityCountry(rs.getLong("PAISACTIVIDAD"));
				local.setLocalName(rs.getString("NOMBRE_LOCAL"));
				local.setClanae(rs.getString("CLANAEREL"));
				
//				local.setActivity(rs.getString("NOMBRE_ACTIVIDAD"));
//				local.setProduct(rs.getString("CLANAE_PRODUCTO"));
//				local.setRawMaterial(rs.getString("CLANAE_MATERIA_PRIMA"));
				
				local.setActivity(getMainActivity(local.getId()));
				local.setProduct(getProduct(local.getId()));
				local.setRawMaterial(getRawMaterial(local.getId()));
				
				
				Long companyID = rs.getLong("ID_EMPRESA");
				company = mapCompanies.get(companyID);
				if(company == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "EMP.ID_EMPRESA", Filter.EQUAL_OPERATOR,companyID,true);
					filters.add(filter);
					
					List<Company> companies = getCompanies(filters, AND_OPERATOR);

					if(companies != null && companies.size() == 1)
					{
						company = companies.get(0);
						mapCompanies.put(companyID, company);
					}
				}
				local.setCompany(company);
				localList.add(local);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return localList;
	}
	
	
	//______________________________________________________________________________	
   // Creado por la gente de Cordoba
	
	public List<Company> getCompanies(List<Filter> filters,String operator)
	{
		filters.add(new Filter(Filter.NUMERIC_FILTER, "EHA.ACTIVPRINCIPAL", Filter.EQUAL_OPERATOR,1,true));
		List<Company> companyList = new ArrayList<Company>();
		try {
			Company company = null;
			User user = null;
			Map<Long,User> usersMap = new HashMap<Long, User>();
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT EMP.ID_EMPRESA,EMP.CUIT,EMP.N_CUIT,EMP.RAZON_SOCIAL,EMP.CONFIRMA,EMP.CONFIRMAD,EMP.CONFIRMARS,EMP.NRSOCIAL, ");
			sql.append("EMP.NOMBRE_FANTASIA,EMP.ID_ESTADO,EMP.CALLE,EMP.NUMERO,EMP.PISO,EMP.OFICINA,EMP.CODIGOPOSTAL,EMP.PROVINCIA as ID_PROVINCIA,PROV.PROVINCIA, ");
			sql.append("EMP.DEPARTAMENTO AS ID_DEPARTAMENTO,DEP.NOMBRE_DEPTO,EMP.MUNICIPIO AS ID_MUNICIPIO,MUN.MUNICIPIO,EMP.TELEFONO,EMP.FAX,EMP.WEB,EMP.EMAIL, ");
			sql.append("EMP.N_CALLE,EMP.N_NUMERO,EMP.N_PISO,EMP.N_OFICINA,EMP.N_CODIGOPOSTAL,EMP.N_PROVINCIA as N_PROVINCIA_ID,PROV2.PROVINCIA AS N_PROVINCIA, ");
			sql.append("EMP.N_DEPARTAMENTO as N_DEPARTAMENTO_ID,DEP2.NOMBRE_DEPTO AS N_NOMBRE_DEPTO,EMP.N_MUNICIPIO as N_MUNICIPIO_ID,MUN2.MUNICIPIO AS N_MUNICIPIO, ");
			sql.append("EMP.N_TELEFONO,EMP.N_FAX,EMP.N_WEB,EMP.N_EMAIL,");
			//,EMP.RESP_LEG_NOMB,EMP.RESP_LEG_CARGO,EMP.RESP_LEG_MAIL, ");
			sql.append("FJ.ID_FORMA_JURIDICA,FJ.FORMA_JURIDICA,EMP.INICIO,EMP.CESE, ");
			sql.append("EMP.EMITE_ESTADO_CONT,EMP.MES_CIERRE_EJER,EMP.GRUPO_ECO,EMP.GRUPO_NOMBRE,EMP.PART_EXTRANJERA,EMP.PAIS_INVERSOR, ");
//			sql.append("EMP.FECHA_ENVIO_CARTA,EMP.FECHA_ACEP_WEB,EMP.FECHA_VENC_ACRED,EMP.FECHA_RECEP_ACRED,EMP.FECHA_VENC_EMPAD, ");
//			sql.append("EMP.RESPONSABLE_NOMB,EMP.RESPONSABLE_CARGO,EMP.RESPONSABLE_MAIL,EMP.RESPONSABLE_TEL,EMP.RESPONDENTE_NOMB,EMP.RESPONDENTE_CARGO, ");
//			sql.append("EMP.RESPONDENTE_MAIL,EMP.RESPONDENTE_TEL,EMP.OBS_EMPRE,EMP.OBS_CONTACTO,EMP.CREATED,EMP.VIA_CARGA,EMP.STATUS, ");
			sql.append("EMP.ID_USUARIO_EMPRESA,EMP.RESTRUCTURA,EMP.ASIGNEDTO, ");
			sql.append("EHA.ACTIVIDAD_ID_ACTIVIDAD AS CLANAE_10,EHA.DESC_ACTIVIDAD AS EHA_ACTIVIDAD,EHA.CLANAE_04 AS CLANAE_04 ");
			sql.append("FROM " + getSecondarySchema() + ".EMPRESA2 EMP ");
			sql.append("LEFT join " + getSecondarySchema() + ".PROVINCIA PROV on EMP.provincia = PROV.id_provincia ");
			sql.append("LEFT join " + getSecondarySchema() + ".PROVINCIA PROV2 on EMP.n_provincia = PROV2.id_provincia ");
			sql.append("LEFT join " + getSecondarySchema() + ".MUNICIPIO MUN on EMP.municipio = MUN.id_municipio ");
			sql.append("LEFT join " + getSecondarySchema() + ".MUNICIPIO MUN2 on EMP.n_municipio = MUN2.id_municipio ");
			sql.append("LEFT join " + getSecondarySchema() + ".DEPARTAMENTO DEP on EMP.departamento = DEP.ID_DEPTO ");
			sql.append("LEFT join " + getSecondarySchema() + ".DEPARTAMENTO DEP2 on EMP.n_departamento = DEP2.ID_DEPTO ");
			sql.append("LEFT join " + getSecondarySchema() + ".FORMA_JURID FJ ON EMP.FORMA_JURIDICA = FJ.ID_FORMA_JURIDICA ");
			sql.append("LEFT join " + getSecondarySchema() + ".EMPRESA_ACTIVIDAD EHA ON EHA.ID_EMPRESA = EMP.ID_EMPRESA ");
			sql.append("LEFT join " + getMainSchema() + ".EMPRESAS_CAMPANAS ec on EMP.id_empresa = ec.id_empresa ");
			sql.append("LEFT join " + getMainSchema() + ".CAMPANAS sc on sc.ID_CAMPANA = ec.ID_CAMPANA and EMP.idempanio = ec.id_empresa || sc.ano ");
			
			
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);//operator Esta en daoFilter En este caso se usa como simbolo =
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY EMP.ID_EMPRESA");
			
					//	System.out.println(sql);
			
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			while (rs.next()) {
				company = new Company();
				company.setId(rs.getLong("ID_EMPRESA"));
				company.setCuit(rs.getString("CUIT"));
				company.setnCuit(rs.getString("N_CUIT"));
				company.setBusinessName(rs.getString("RAZON_SOCIAL"));
				company.setConfirm(rs.getString("CONFIRMA"));
				company.setConfirmD(rs.getString("CONFIRMAD"));
				company.setConfirmRS(rs.getString("CONFIRMARS"));
				company.setBusinessNameN(rs.getString("NRSOCIAL"));
				company.setFantasyName(rs.getString("NOMBRE_FANTASIA"));
				company.setStatusID(rs.getLong("ID_ESTADO"));
				company.setStreet(rs.getString("CALLE"));
				company.setNumber(rs.getString("NUMERO"));
				company.setFlat(rs.getString("PISO"));
				company.setOffice(rs.getString("OFICINA"));
				company.setZipCode(rs.getString("CODIGOPOSTAL"));
				
				Province province = new Province(rs.getString("ID_PROVINCIA"), rs.getString("PROVINCIA"));
				company.setProvince(province);				
				State state = new State(rs.getString("ID_DEPARTAMENTO"), rs.getString("NOMBRE_DEPTO"));
				company.setState(state);				
				City city = new City(rs.getString("ID_MUNICIPIO"), rs.getString("MUNICIPIO"));
				company.setCity(city);
				
				company.setTelephoneNumber(rs.getString("TELEFONO"));
				company.setFaxNumber(rs.getString("FAX"));
				company.setWebPage(rs.getString("WEB"));
				company.setEmail(rs.getString("EMAIL"));
				company.setStreetN(rs.getString("N_CALLE"));
				company.setNumberN(rs.getString("N_NUMERO"));
				company.setFlatN(rs.getString("N_PISO"));
				company.setOfficeN(rs.getString("N_OFICINA"));
				company.setZipCodeN(rs.getString("N_CODIGOPOSTAL"));
				
				province = new Province(rs.getString("N_PROVINCIA_ID"), rs.getString("N_PROVINCIA"));
				company.setProvinceN(province);				
				state = new State(rs.getString("N_DEPARTAMENTO_ID"), rs.getString("N_NOMBRE_DEPTO"));
				company.setStateN(state);				
				city = new City(rs.getString("N_MUNICIPIO_ID"), rs.getString("N_MUNICIPIO"));
				company.setCityN(city);
				
				company.setTelephoneNumberN(rs.getString("N_TELEFONO"));
				company.setFaxNumberN(rs.getString("N_FAX"));
				company.setWebPageN(rs.getString("N_WEB"));
				company.setEmailN(rs.getString("N_EMAIL"));
//				company.setResponsibleLegName(rs.getString("RESP_LEG_NOMB"));
//				company.setResponsibleLegRole(rs.getString("RESP_LEG_CARGO"));
//				company.setResponsibleLegEmail(rs.getString("RESP_LEG_MAIL"));
				
				LegalForm legalForm = new LegalForm(rs.getLong("ID_FORMA_JURIDICA"),rs.getString("FORMA_JURIDICA"));
				company.setLegalForm(legalForm);
				company.setStartDate(rs.getDate("INICIO"));
				company.setEndDate(rs.getDate("CESE"));
				company.setIssueFinancialStatement(rs.getBoolean("EMITE_ESTADO_CONT"));
				company.setEndExerciseMonth(rs.getDate("MES_CIERRE_EJER"));
				company.setGroupEco(rs.getString("GRUPO_ECO"));
				company.setGroupName(rs.getString("GRUPO_NOMBRE"));
				company.setForeignPart(rs.getBoolean("PART_EXTRANJERA"));
				company.setInvestorCountry(rs.getString("PAIS_INVERSOR"));
//				company.setSendLetterDate(rs.getDate("FECHA_ENVIO_CARTA"));
//				company.setWebAcceptDate(rs.getDate("FECHA_ACEP_WEB"));
//				company.setExpirationAcredDate(rs.getDate("FECHA_VENC_ACRED"));
//				company.setExpirationEmpadDate(rs.getDate("FECHA_VENC_EMPAD"));
//				company.setReceptionAcredDate(rs.getDate("FECHA_RECEP_ACRED"));
//				company.setResponsibleName(rs.getString("RESPONSABLE_NOMB"));
//				company.setResponsibleRole(rs.getString("RESPONSABLE_CARGO"));
//				company.setResponsibleEmail(rs.getString("RESPONSABLE_MAIL"));
//				company.setResponsibleTelephone(rs.getString("RESPONSABLE_TEL"));
//				company.setRemitterName(rs.getString("RESPONDENTE_NOMB"));
//				company.setRemitterRole(rs.getString("RESPONDENTE_CARGO"));
//				company.setRemitterEmail(rs.getString("RESPONDENTE_MAIL"));
//				company.setRemitterTelephone(rs.getString("RESPONDENTE_TEL"));
//				company.setCompanyObservations("OBS_EMPRE");
//				company.setContactObservations(rs.getString("OBS_CONTACTO"));
//				company.setCreateDate(rs.getDate("CREATED"));
				company.setAsignedTo(rs.getLong("ASIGNEDTO"));
//				company.setStatus(rs.getString("STATUS"));
				company.setRestructuring(rs.getLong("RESTRUCTURA"));
//				company.setChargeWay(rs.getString("VIA_CARGA"));
				
				company.setClanae04(rs.getString("CLANAE_04"));
				company.setClanae10(rs.getString("CLANAE_10"));
				company.setActivity(rs.getString("EHA_ACTIVIDAD"));
				
				user = usersMap.get(rs.getLong("ID_USUARIO_EMPRESA"));
				if(user == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "US.ID_USUARIO", Filter.EQUAL_OPERATOR,rs.getLong("ID_USUARIO_EMPRESA"),true);
					filters.add(filter);
					
					List<User> users = getUsers(filters, AND_OPERATOR);

					if(users != null && users.size() == 1)
					{
						user = users.get(0);
						usersMap.put(user.getId(), user);
					}
				}
				company.setUser(user);
				
				companyList.add(company);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return companyList;
	}
	
	//______________________________________________________________________________
	
	// Creado por la gente de Cordoba
	public List<CoverDefinition> getCoverDefinitions(Long pollID)
	{
		List<CoverDefinition> coverDefList = new ArrayList<CoverDefinition>();
		try {		
				conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();

			sql.append("SELECT CAR_DEF.ID,CAR_DEF.ID_ENCUESTA,CAR_DEF.CAMPO,CAR_DEF.CODIGO,CAR_DEF.TABLA FROM " + getMainSchema() + ".CARATULAS_DEFINICION CAR_DEF ");
			sql.append("WHERE CAR_DEF.ID_ENCUESTA = " + pollID );
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			CoverDefinition coverDefinition = null;
			
			//RECORRO LOS RESULTADOS
			while (rs.next()) {
				coverDefinition = new CoverDefinition();
				
				coverDefinition.setId(rs.getLong(1));
				coverDefinition.setPollID(rs.getLong(2));
				coverDefinition.setField(rs.getString(3));
				coverDefinition.setCode(rs.getString(4));
				coverDefinition.setTable(rs.getString(5));
				
				coverDefList.add(coverDefinition);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return coverDefList;
	}

	
	//______________________________________________________________________________
	
	// Creado por la gente de Cordoba
	public List<PollValue> getPollValues(Long campaignID, Long pollID, Long localID)
	{
		if (campaignID == null || pollID == null || localID == null
				|| campaignID <= 0 || pollID <= 0)
		{
			System.out.println("OCURRIO UN ERROR - NO SE PUDIERON CARGAR LOS VALORES DE LA ENCUESTA");
			return null;
		}
		
		List<PollValue> listaValoresEncuesta = new ArrayList<PollValue>();
		Map<Long, Local> mapLocals = new HashMap<Long, Local>();
		try {
				conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();

			//ARMO LA CONSULTA QUE ME DEVUELVE TODOS LOS VALORES JUNTOS.
//			sql.append("SELECT * FROM ENC_POLLS_DEF_CMP POLLS_VALUES ");
//			sql.append("INNER JOIN ENC_POLLS_COMPANIES POLLS_COMPANIES ON POLLS_VALUES.POL_DEF_CMP_POLL_CMP_ID = POLLS_COMPANIES.POL_CMP_ID ");
//			sql.append("INNER JOIN ENC_POLLS_DEFINITION POLLS_DEFINITIONS ON POLLS_VALUES.POL_DEF_CMP_POLL_DEF_ID = POLLS_DEFINITIONS.POL_DEF_ID ");
//			sql.append("INNER JOIN ENC_POLLS POLLS ON POLLS_COMPANIES.POL_CMP_POLL_ID = POLLS.POL_ID ");
//			sql.append("INNER JOIN ENC_COMPANIES COMPANIES ON POLLS_COMPANIES.POL_CMP_Empresa_ID = COMPANIES.CMP_ID ");
//			sql.append("WHERE POL_ID = " + pollID + " AND CMP_ID = " + EmpresaID + " AND POL_CMP_SEQUENCE = " + sequenceID);
			
			sql.append("SELECT * FROM " + getMainSchema() + ".ENCUESTAS_EMPRESAS_VALORES EEV ");
			sql.append("INNER JOIN " + getMainSchema() + ".ENCUESTAS_EMPRESAS ENC_EMP ON EEV.ID_ENC_EMP = ENC_EMP.ID_ENC_EMP ");
			sql.append("INNER JOIN " + getMainSchema() + ".ENCUESTAS_DEFINICION ENC_DEF ON EEV.ID_ENC_DEF = ENC_DEF.ID_ENC_DEF ");
			sql.append("INNER JOIN " + getMainSchema() + ".ENCUESTAS ENC ON ENC_EMP.ID_ENCUESTA = ENC.ID_ENCUESTA ");
			sql.append("INNER JOIN " + getMainSchema() + ".ESTADOS_FORMULARIOS EST_FORM ON EST_FORM.ID_EST_FORM = ENC_EMP.ID_ESTADO_FORMULARIO ");
//			sql.append("INNER JOIN EMPRESAS EMP ON ENC_EMP.CUIT_EMPRESA = EMP.CUIT ");
			if(localID == 0)
			{
				sql.append("WHERE ENC_EMP.ID_ENCUESTA = '"+ pollID +"' AND ENC_EMP.ID_EMP_CAMP = "+ campaignID +" AND ENC_EMP.ID_LOCAL IS NULL");
			}
			else
			{
				sql.append("WHERE ENC_EMP.ID_ENCUESTA = '"+ pollID +"' AND ENC_EMP.ID_EMP_CAMP = "+ campaignID +" AND ENC_EMP.ID_LOCAL = "+ localID);	
			}			
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			CompanyCampaign companyCampaign = null;
			Poll encuesta = null;
			Local local = null;
			FormStatus estadoFormulario = null;
			CompanyPoll encuestaEmpresa = null;
			PollDefinition definicionEncuesta = null;
			PollValue valorEncuesta = null;
			
			//RECORRO LOS RESULTADOS
			while (rs.next()) {
				
				//CREO EL OBJETO CampanaEmpresa (SOLO UNA VEZ)
				if(companyCampaign == null)
				{
					List<Filter> filters = new ArrayList<Filter>();		
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "EMP_CAM.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,campaignID,true);
					filters.add(filter);
									
					List<CompanyCampaign> companyCampaigns = getCompanyCampaigns(filters, AND_OPERATOR);

					if(companyCampaigns != null && companyCampaigns.size() == 1)
					{
						companyCampaign = companyCampaigns.get(0);
					}
				}
				
				//CREO EL OBJETO POLL (SOLO UNA VEZ)
				if(encuesta == null) 
				{
					encuesta = new Poll(rs.getLong("ID_ENCUESTA"), rs
							.getString("CODIGO_ENCUESTA"), rs.getString("NOMBRE_ENCUESTA"), rs
							.getString("TIPO_ENCUESTA"), rs.getBoolean("MULTIPLE_ENCUESTA"));
				}
				
				//CREO EL OBJETO Local (SOLO UNA VEZ) en caso de que no sea una encuesta de tipo de empresa
				if(encuesta.isMultiple())
				{
					local = mapLocals.get(rs.getLong("ID_LOCAL"));
					if(local == null)
					{
						List<Filter> filters = new ArrayList<Filter>();
						Filter filter = new Filter(Filter.NUMERIC_FILTER, "LOC.ID_LOCAL", Filter.EQUAL_OPERATOR,rs.getLong("ID_LOCAL"),true);
						filters.add(filter);
						
						List<Local> locales = getLocals(filters, AND_OPERATOR);

						if(locales != null && locales.size() == 1)
						{
							local = locales.get(0);
							mapLocals.put(local.getId(), local);
						}
					}
				}				
				
				//CREO EL OBJETO encuestaEmpresa (SOLO UNA VEZ)
				if(encuestaEmpresa == null)
				{
					estadoFormulario = new FormStatus(rs.getLong("ID_EST_FORM"), rs.getString("ESTADO_FORMULARIO"));
					
					encuestaEmpresa = new CompanyPoll(rs
							.getLong("ID_ENC_EMP"), encuesta, companyCampaign, local,
							rs.getDate("FECHA_CREACION"), rs
									.getDate("FECHA_MODIFICACION"),estadoFormulario);
				}
				
				//CREO EL OBJETO POLLDEFINITION (UNO POR FILA DEL RS)
				definicionEncuesta = new PollDefinition(rs.getLong("ID_ENC_DEF"), encuesta, rs
						.getString("CODIGO"), rs.getInt("HOJA"), rs.getInt("FILA"), rs
						.getInt("COLUMNA"), rs.getString("DESCRIPCION"), rs.getBoolean("FORMATO"));
				
				//CREO EL OBJETO POLLVALUE (UNO POR FILA DEL RS)
				valorEncuesta = new PollValue(rs.getLong("ID_ENC_DEF_EMP"), encuestaEmpresa,
						definicionEncuesta, rs.getString("VALOR"));
				
				listaValoresEncuesta.add(valorEncuesta);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return listaValoresEncuesta;
	}
	
	//______________________________________________________________________________
	
	// Creado por la gente de Cordoba
	public List<Poll> getPolls(List<Filter> filters,String operator)
	{
		List<Poll> pollList = new ArrayList<Poll>();
		try {
			Poll poll = null;
				conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			sql.append("SELECT ENC.ID_ENCUESTA,ENC.CODIGO_ENCUESTA,ENC.NOMBRE_ENCUESTA,ENC.TIPO_ENCUESTA,ENC.MULTIPLE_ENCUESTA ");
			sql.append("FROM " + getMainSchema() + ".ENCUESTAS ENC");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY ENC.ID_ENCUESTA");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			while (rs.next()) {
				poll = new Poll();
				poll.setId(rs.getLong(1));
				poll.setCode(rs.getString(2));
				poll.setName(rs.getString(3));
				poll.setType(rs.getString(4));
				poll.setMultiple(rs.getBoolean(5));
				
				pollList.add(poll);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return pollList;
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<Poll> getPolls(List<Filter> filters,String operator,Conexion conn)
	{
		List<Poll> pollList = new ArrayList<Poll>();
		try {
			Poll poll = null;
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			sql.append("SELECT ENC.ID_ENCUESTA,ENC.CODIGO_ENCUESTA,ENC.NOMBRE_ENCUESTA,ENC.TIPO_ENCUESTA,ENC.MULTIPLE_ENCUESTA ");
			sql.append("FROM " + getMainSchema() + ".ENCUESTAS ENC");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY ENC.ID_ENCUESTA");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			while (rs.next()) {
				poll = new Poll();
				poll.setId(rs.getLong(1));
				poll.setCode(rs.getString(2));
				poll.setName(rs.getString(3));
				poll.setType(rs.getString(4));
				poll.setMultiple(rs.getBoolean(5));
				
				pollList.add(poll);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		}
		return pollList;
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<PollDefinition> getPollDefinitions(List<Filter> filters,String operator)
	{
		List<PollDefinition> pollDefinitionList = new ArrayList<PollDefinition>();
		try {
			PollDefinition pollDefinition= null;
				conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			sql.append("SELECT pol.pol_id as id,pol.pol_code as code,pol.pol_name as name,pol.pol_type as type,pol.pol_is_multiple as is_multiple,");
			sql.append("FROM " + getMainSchema() + ".ENC_POLLS pol");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY pol.pol_id");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);

			while (rs.next()) {
				pollDefinition = new PollDefinition();
//				poll.setId(rs.getLong(1));
//				poll.setCode(rs.getString(2));
//				poll.setName(rs.getString(3));
//				poll.setType(rs.getString(4));
//				poll.setMultiple(rs.getBoolean(5));
				
				pollDefinitionList.add(pollDefinition);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return pollDefinitionList;
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<String> getStringList(String sql)
	{
		List<String> list = new ArrayList<String>();
		
		try {
			conn.abrirConexion();	
			List<BoundParam> parameters = new ArrayList<BoundParam>();			
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			while (rs.next()) 
			{
				list.add(rs.getString(1));
			}
			return list;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<String> getLockedFieldsFormE()
	{
		List<String> lockedList = new ArrayList<String>();
		try {
			conn.abrirConexion();	
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			StringBuilder sql = new StringBuilder();
			
			sql.append("select ED.CODIGO from " + getMainSchema() + ".SUMA_LOCALES SL ");
			sql.append("join " + getMainSchema() + ".ENCUESTAS_DEFINICION ED ON ED.ID_ENC_DEF = SL.ID_ENC_DEF_E ");
			sql.append("ORDER BY ED.CODIGO");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) 
			{
				lockedList.add(rs.getString(1));
			}
			return lockedList;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<Indicator> getIndicators(Long companyCampaignID, Long localID, Long type)
	{
		final String QUERY_TYPE_1 = "(SELECT ID_ENC_DEF FROM ENCUESTAS_DEFINICION WHERE ID_ENCUESTA = 2 AND HOJA = 9 AND COLUMNA = 5) ";
		final String QUERY_TYPE_2 = "(SELECT ID_ENC_DEF FROM ENCUESTAS_DEFINICION WHERE ID_ENCUESTA = 34 AND HOJA = 9) ";
		
		List<Indicator> indicators = new ArrayList<Indicator>();
		try {
			conn.abrirConexion();	
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			StringBuilder sql = new StringBuilder();
			
			sql.append("SELECT ED.DEFINICION,EEV.VALOR FROM ENCUESTAS_EMPRESAS_VALORES EEV ");
			sql.append("LEFT JOIN ENCUESTAS_DEFINICION ED ON EEV.ID_ENC_DEF = ED.ID_ENC_DEF ");
			sql.append("WHERE EEV.ID_ENC_DEF IN ");
			if(type.equals(PollService.INDICATORS_WITH_TRANSFERENCE))
			{
				sql.append(QUERY_TYPE_1);
			}
			if(type.equals(PollService.INDICATORS_REGIONALIZED))
			{
				sql.append(QUERY_TYPE_2);
			}	
			sql.append("AND EEV.ID_ENC_EMP IN (SELECT ID_ENC_EMP FROM ENCUESTAS_EMPRESAS WHERE ID_EMP_CAMP = " + companyCampaignID + " and ID_LOCAL = " + localID + ")");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) 
			{
				indicators.add(new Indicator(rs.getString(1), rs.getString(2)));
			}
			return indicators;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void regionalize(Long companyCampaignID) throws SQLException
	{
		conn.abrirConexion();
		Statement stmtQuery;
		Statement stmtQueryModelos;
		Statement stmtQueryFormulas;
		Statement stmtUpdate;
		Statement stmtQueryFormulasVar;
		
		stmtQuery = conn.createStatement();
		stmtQueryModelos = conn.createStatement();
		stmtQueryFormulas = conn.createStatement();
		stmtQueryFormulasVar = conn.createStatement();
		stmtUpdate = conn.createStatement();
		
		ResultSet modelos;
		ResultSet formulas;
		ResultSet formulasVar;
		long maxPasadas;
		long maxNiveles;
		long id_formula;
		long idEncEmpE;
		long vistaETime = new Date().getTime();
		
		String script;
		String scriptVar;
		String encuestas;
		
		modelos = stmtQueryModelos.executeQuery("select distinct modelo from MODELO_CLANAE mc where exists (SELECT null FROM ENCUESTAS_EMPRESAS ee join " + getSecondarySchema() +".FORMULARIOS_EMPRESA fe on ee.ID_LOCAL = fe.IDLOCAL where ee.ID_EMP_CAMP = "+ companyCampaignID + " and mc.CLANAE = fe.CLANAEREL) order by modelo")  ;
		
//		modelos = stmtQueryModelos.executeQuery("select distinct modelo from MODELO_CLANAE ");
		
		idEncEmpE = getFirstValue("select id_enc_emp from encuestas_empresas where id_emp_camp = " + companyCampaignID  + " and ID_ENCUESTA = " + PollService.POLL_E_ID,stmtQuery);

		while (modelos.next())  // MODELOS
		{
			
			int modelo = modelos.getInt("modelo");
			
	//		System.out.println("Modelo: " + modelo);
			
			stmtUpdate.executeUpdate(
					" update ENCUESTAS_EMPRESAS_VALORES eev set valor = ( select   eevo.VALOR from ENCUESTAS_EMPRESAS_VALORES eevo join ENCUESTAS_DEFINICION edo on eevo.ID_ENC_DEF = edo.ID_ENC_DEF join ENCUESTAS_DEFINICION edo2 on edo.codigo = edo2.CODIGO join ENCUESTAS_EMPRESAS eeo on eevo.ID_ENC_EMP = eeo.ID_ENC_EMP join ENCUESTAS_EMPRESAS eeo2 on eeo.id_emp_camp =  eeo2.id_emp_camp and eeo.id_local = eeo2.id_local " + 
					" where eeo.ID_EMP_CAMP = "+ companyCampaignID +" and eeo.ID_ENCUESTA = " + PollService.POLL_L_ID + " and eeo2.ID_ENCUESTA = " + PollService.POLL_LR_ID + 
					" and eeo2.ID_ENC_EMP = eev.ID_ENC_EMP and  edo2.ID_ENC_DEF = eev.ID_ENC_DEF ) " +
					" where exists( select   null from ENCUESTAS_EMPRESAS_VALORES eevo join ENCUESTAS_DEFINICION edo on eevo.ID_ENC_DEF = edo.ID_ENC_DEF join ENCUESTAS_DEFINICION edo2 on edo.codigo = edo2.CODIGO join ENCUESTAS_EMPRESAS eeo on eevo.ID_ENC_EMP = eeo.ID_ENC_EMP join ENCUESTAS_EMPRESAS eeo2 on eeo.id_emp_camp =  eeo2.id_emp_camp and eeo.id_local = eeo2.id_local join " + getSecondarySchema() +".FORMULARIOS_EMPRESA fe on eeo2.ID_LOCAL = fe.IDLOCAL join MODELO_CLANAE mc on mc.CLANAE = fe.CLANAEREL " + 
					" where eeo.ID_EMP_CAMP = "+ companyCampaignID +" and eeo.ID_ENCUESTA = " + PollService.POLL_L_ID + " and eeo2.ID_ENCUESTA = " + PollService.POLL_LR_ID + " and mc.MODELO = "+ modelo + " and eeo2.ID_ENC_EMP = eev.ID_ENC_EMP and  edo2.ID_ENC_DEF = eev.ID_ENC_DEF ) ");
					
			maxPasadas = getFirstValue ("select max (PASADA) as cant from formulas where modelo = " + modelo,stmtQuery);
			
			
			encuestas = getFirstString ("select encuestas from modelos where modelo = " + modelo,stmtQuery);
			
			
			for (int pasada = 1; pasada <=  maxPasadas; pasada++)  // PASADAS
			{

				maxNiveles = getFirstValue("select max (NIVEL) as cant from formulas where modelo = " + modelo + " and activa = 1 and pasada = " + pasada,stmtQuery);
				
				for (int nivel = 1; nivel <=  maxNiveles; nivel++)  // NIVELES
				{
					
					try{
						stmtUpdate.executeUpdate( " drop materialized view E"+ vistaETime );
					}
					catch(SQLException e){}
					
					stmtUpdate.executeUpdate( " create materialized view E"+ vistaETime +" as " +
					
					" SELECT " + idEncEmpE + " as ID_ENC_EMP, replace( ed.DEFINICION,'L~','E~') as definicion, sum(nvl(valor,0)) as valor FROM ENCUESTAS_EMPRESAS ee " + 
					" join ENCUESTAS_DEFINICION ed on ee.ID_ENCUESTA = ed.ID_ENCUESTA join ENCUESTAS_EMPRESAS_VALORES eev on ee.ID_ENC_EMP = eev.ID_ENC_EMP and eev.ID_ENC_DEF = ed.ID_ENC_DEF join " + getSecondarySchema() +".FORMULARIOS_EMPRESA fe on  ee.ID_LOCAL = fe.IDLOCAL join MODELO_CLANAE mc on fe.CLANAEREL = mc.CLANAE " +
					" WHERE ee.ID_EMP_CAMP = "+ companyCampaignID +" and ee.ID_ENCUESTA in ("+ PollService.POLL_L_ID +  ") and ed.DEFINICION is not null and mc.MODELO =  " + modelo + " and is_number(nvl(trim(valor),0)) = 1 group by ed.DEFINICION " +
					" union all " +
					" SELECT ee.ID_ENC_EMP, definicion, sum(nvl(valor,0)) as valor FROM ENCUESTAS_EMPRESAS ee join ENCUESTAS_DEFINICION ed on ee.ID_ENCUESTA = ed.ID_ENCUESTA join ENCUESTAS_EMPRESAS_VALORES eev on ee.ID_ENC_EMP = eev.ID_ENC_EMP and eev.ID_ENC_DEF = ed.ID_ENC_DEF join " + getSecondarySchema() +".FORMULARIOS_EMPRESA fe on  ee.ID_LOCAL = fe.IDLOCAL join MODELO_CLANAE mc on fe.CLANAEREL = mc.CLANAE " + 
					" WHERE ee.ID_EMP_CAMP = "+ companyCampaignID +" and ee.ID_ENCUESTA in "+ encuestas +  " and ed.DEFINICION is not null and mc.MODELO = " + modelo + " and is_number(nvl(trim(valor),0)) = 1 group by ee.ID_ENC_EMP, ed.DEFINICION " +
					" union all " +
					" select fv.ID_ENC_EMP, f.CODIGO, to_number(nvl(trim(valor),0)) as valor from FORMULAS_VALORES fv join FORMULAS f on fv.ID_FORMULA = f.ID_FORMULA join ENCUESTAS_EMPRESAS ee on fv.ID_ENC_EMP = ee.ID_ENC_EMP join " + getSecondarySchema() +".FORMULARIOS_EMPRESA fe on  ee.ID_LOCAL = fe.IDLOCAL join MODELO_CLANAE mc on fe.CLANAEREL = mc.CLANAE " +

					" WHERE is_number(nvl(trim(valor),0)) = 1 and ee.ID_EMP_CAMP = "+ companyCampaignID +" and mc.MODELO = " + modelo + " and f.MODELO =  " + modelo + 
					" union all " +
				    " select fv.ID_ENC_EMP, f.CODIGO, to_number(nvl(trim(valor),0)) as valor  from FORMULAS_VALORES fv join FORMULAS f on fv.ID_FORMULA = f.ID_FORMULA " + 
					" where fv.ID_ENC_EMP =  " + idEncEmpE + " and f.MODELO =  " + modelo);
					
					
//					stmtUpdate.executeUpdate( " create index mv_"+vistaETime+" ON E"+ vistaETime +" (DEFINICION, ID_ENC_EMP, VALOR) ");

					formulas = stmtQueryFormulas.executeQuery("select * from FORMULAS where modelo = " + modelo  + " and pasada = "+ pasada + " and ID_ENCUESTA = " + PollService.POLL_E_ID  + " and nivel = "+ nivel + " and activa = 1 order by nivel asc");
					
					while (formulas.next())  // FORMULAS E
					{
						
						id_formula = formulas.getInt("id_formula");
						
						script = formulas.getString("script");
						
						script = script.replaceAll("valores", "E"+ vistaETime);
						
//						System.out.println
//						("update FORMULAS_VALORES " +
//						"set VALOR = (" + script + ")"+
//						"where id_formula = " + id_formula + " and id_enc_emp = " + idEncEmpE);
						
						try
						{
						stmtUpdate.executeUpdate("update FORMULAS_VALORES " +
						"set VALOR = (" + script + ")"+
						"where id_formula = " + id_formula + " and id_enc_emp = " + idEncEmpE);
						}
						catch (SQLException e){
							
							if (e.getErrorCode() == 1476 || e.getErrorCode() == 1427 )
							{
								System.out.println("***** Error en f\u00F3rmula: " + id_formula + ". Divisi\u00F3 n por 0. No hay valores");
							}
							else
							{
								e.printStackTrace();
							}
							
							stmtUpdate.executeUpdate("update FORMULAS_VALORES " +
								"set VALOR = 0 where id_formula = " 
								 + id_formula + " and id_enc_emp = " + idEncEmpE);}
					}
					
					formulas = stmtQueryFormulas.executeQuery("select * from FORMULAS where modelo = " + modelo  + " and pasada = "+ pasada + " and ID_ENCUESTA = " + PollService.POLL_L_ID  + " and nivel = "+ nivel + " and activa = 1 order by nivel asc");

					while (formulas.next())  // FORMULAS L
					{
						id_formula = formulas.getInt("id_formula"); 	
						
						script = formulas.getString("script");
						script = script.replaceAll("valores", "E"+ vistaETime);
						
//						System.out.println(script);
						
						try
						{
						stmtUpdate.executeUpdate("update FORMULAS_VALORES fv set VALOR = ( select valor from (" + script +
								") v where fv.ID_ENC_EMP = v.ID_ENC_EMP) where ID_FORMULA =" + id_formula + " and exists (select null from " + "E"+ vistaETime  + " e where fv.ID_ENC_EMP = e.ID_ENC_EMP)") ;
						
					}
					catch (SQLException e){
						
//						System.out.println("update FORMULAS_VALORES fv set VALOR = ( select valor from (" + script +
//								") v where fv.ID_ENC_EMP = v.ID_ENC_EMP) where ID_FORMULA =" + id_formula + " and exists (select null from " + "E"+ vistaETime  + " e where fv.ID_ENC_EMP = e.ID_ENC_EMP)");
//						
						if (e.getErrorCode() == 1476 || e.getErrorCode() == 1427 )
						{
							System.out.println("***** Error en f\u00F3rmula: " + id_formula + ". Divisi\u00F3n por 0. No hay valores");
						}
						else
						{
							System.out.println("Error en f\u00F3rmula: " + id_formula);
							e.printStackTrace();
						}
						
						stmtUpdate.executeUpdate("update FORMULAS_VALORES fv set VALOR = 0 where ID_FORMULA =" + id_formula + " and exists (select null from " + "E"+ vistaETime  + " e where fv.ID_ENC_EMP = e.ID_ENC_EMP)") ;

						}
					}
				}
			}
			
			
			formulasVar = stmtQueryFormulasVar.executeQuery(" select * from FORMULAS_VARIABLES where modelo = " + modelo + " and activa = 1 order by id_formula_variable");
			
			while (formulasVar.next())  //Calculo de variables para todos los locales 
			{
				
				scriptVar = formulasVar.getString("script");
				scriptVar = scriptVar.replaceAll("valores", "E"+ vistaETime);
				
				stmtUpdate.executeUpdate(" " +
						" update ENCUESTAS_EMPRESAS_VALORES eev set valor = ( " +
						" select round(val.valor) from ( " +
						scriptVar +
						") val join ENCUESTAS_DEFINICION ed on val.variable_r = ed.DEFINICION join ENCUESTAS_EMPRESAS ee on val.ID_ENC_EMP = ee.ID_ENC_EMP join ENCUESTAS_EMPRESAS eer on ee.id_emp_camp =  eer.id_emp_camp and ee.id_local = eer.id_local " +
						"where  eer.ID_ENCUESTA = " + PollService.POLL_LR_ID + " and eev.ID_ENC_EMP = eer.ID_ENC_EMP and  eev.ID_ENC_DEF =  ed.ID_ENC_DEF) " +
						"where exists( " +
						"select val.valor from ( " +
						scriptVar +
						" ) val join ENCUESTAS_DEFINICION ed on val.variable_r = ed.DEFINICION join ENCUESTAS_EMPRESAS ee on val.ID_ENC_EMP = ee.ID_ENC_EMP join ENCUESTAS_EMPRESAS eer on ee.id_emp_camp =  eer.id_emp_camp and ee.id_local = eer.id_local " +
						" where  eer.ID_ENCUESTA = " + PollService.POLL_LR_ID + " and eev.ID_ENC_EMP = eer.ID_ENC_EMP and  eev.ID_ENC_DEF =  ed.ID_ENC_DEF) ");
			}
		}
		
		try{
			stmtUpdate.executeUpdate( " drop materialized view E"+ vistaETime );
		}
		catch(SQLException e){} //cuando los clanaes de los locales no pertenecen e ningún modelo}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	private static Long getFirstValue(String sql,Statement stmtQuery) throws SQLException
	{
		ResultSet rs;
		rs = stmtQuery.executeQuery(sql);
		rs.next();
		return rs.getLong(1);
	}
	
	private static String getFirstString(String sql,Statement stmtQuery) throws SQLException
	{
		ResultSet rs;
		rs = stmtQuery.executeQuery(sql);
		rs.next();
		return rs.getString(1);
	}
	
	public String getFirstString(String sql)
	{
		try {
			conn.abrirConexion();
			Statement stmtQuery = conn.createStatement();
			ResultSet rs;
			rs = stmtQuery.executeQuery(sql);
			rs.next();
			return rs.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		finally
		{
			conn.cerrarConexion();
		}
		
	}
	
	//_____________________________________________________________________________
	//Creado por la gente de Indec
	
	public List<CompanyPoll> getCompanyPollsOnly(Long idEmpCamp)
	{
		List<CompanyPoll> encuestasEmpresa = new ArrayList<CompanyPoll>();
		try {
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			CompanyPoll pollE = new CompanyPoll();
			
			sql.append("Select sec.ID_ENC_EMP,sec.ID_ENCUESTA,senc.NOMBRE_ENCUESTA,sef.id_est_form ");
			sql.append("from sirena.encuestas_empresas sec ");
			sql.append("left join "+ getMainSchema() +".encuestas senc on senc.id_encuesta = sec.id_encuesta ");
			sql.append("left join "+ getMainSchema() +".ESTADOS_FORMULARIOS sef on sef.id_est_form = sec.id_estado_formulario ");
			sql.append("left join "+ getMainSchema() +".empresas_campanas seca on seca.id_emp_camp = sec.id_emp_camp ");
			sql.append("left join "+ getSecondarySchema() +".empresa se on se.id_empresa = seca.id_empresa ");
			sql.append("where senc.NOMBRE_ENCUESTA not in ( select senc.NOMBRE_ENCUESTA from "+ getMainSchema() +".ENCUESTAS where senc.NOMBRE_ENCUESTA like '%L%'  union all select senc.NOMBRE_ENCUESTA from "+ getMainSchema() +".ENCUESTAS where senc.NOMBRE_ENCUESTA like 'Anexo%' ) ");
			sql.append("and sec.id_emp_camp = "+ idEmpCamp + " ");
			sql.append("order by sec.id_encuesta ");
			
		//	System.out.println(sql);
			
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				pollE = new CompanyPoll();
				
				pollE.setPoll( new Poll());
				pollE.getPoll().setName(rs.getString("NOMBRE_ENCUESTA"));
				pollE.getPoll().setId(rs.getLong("ID_ENCUESTA"));
				
				
				pollE.setFormStatus( new FormStatus());
				pollE.getFormStatus().setId(rs.getLong("ID_EST_FORM"));
				
				encuestasEmpresa.add(pollE);
				
			}
			
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		
		return encuestasEmpresa;
	}
	
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<CompanyPoll> getCompanyPolls(List<Filter> filters,String operator)
	{
		List<CompanyPoll> listaEncustasEmpresa = new ArrayList<CompanyPoll>();
		try {
			FormStatus estadoFormulario = null;
			CompanyPoll encuestaEmpresa= null;
			Poll encuesta= null;
			Local local= null;
			Map<Long, Local> mapLocales = new HashMap<Long, Local>();
			CompanyCampaign companyCampaign= null;
			
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
					
			sql.append("SELECT * FROM " + getMainSchema() + ".ENCUESTAS_EMPRESAS ENC_EMP ");
			sql.append("INNER JOIN " + getMainSchema() + ".EMPRESAS_CAMPANAS EMP_CAM ON ENC_EMP.ID_EMP_CAMP = EMP_CAM.ID_EMP_CAMP ");
			sql.append("INNER JOIN " + getMainSchema() + ".ENCUESTAS ENC ON ENC_EMP.ID_ENCUESTA = ENC.ID_ENCUESTA ");
			sql.append("INNER JOIN " + getMainSchema() + ".ESTADOS_FORMULARIOS EST_FORM ON EST_FORM.ID_EST_FORM = ENC_EMP.ID_ESTADO_FORMULARIO ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			if(!sql.toString().toUpperCase().contains(" ORDER BY "))
			{
				sql.append(" ORDER BY ENC.NOMBRE_ENCUESTA ");	
			}			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				//CREO EL OBJETO CampanaEmpresa (SOLO UNA VEZ)
				if(companyCampaign == null)
				{
					filters = new ArrayList<Filter>();		
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "EMP_CAM.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,rs.getLong("ID_EMP_CAMP"),true);
					filters.add(filter);
									
					List<CompanyCampaign> companyCampaigns = getCompanyCampaigns(filters, AND_OPERATOR);

					if(companyCampaigns != null && companyCampaigns.size() == 1)
					{
						companyCampaign = companyCampaigns.get(0);
					}
				}
	
				local = mapLocales.get(rs.getLong("ID_LOCAL"));
				if(local == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "LOC.ID_LOCAL", Filter.EQUAL_OPERATOR,rs.getLong("ID_LOCAL"),true);
					filters.add(filter);
					
					List<Local> locales = getLocals(filters, ObjectDao.AND_OPERATOR);

					if(locales != null && locales.size() == 1)
					{
						local = locales.get(0);
						mapLocales.put(rs.getLong("ID_LOCAL"), local);
					}
				}
				
				encuesta = new Poll(rs.getLong("ID_ENCUESTA"), rs
						.getString("CODIGO_ENCUESTA"), rs.getString("NOMBRE_ENCUESTA"), rs
						.getString("TIPO_ENCUESTA"), rs.getBoolean("MULTIPLE_ENCUESTA"));
				
				estadoFormulario = new FormStatus(rs.getLong("ID_EST_FORM"), rs.getString("ESTADO_FORMULARIO"));
				
				encuestaEmpresa = new CompanyPoll(rs.getLong("ID_ENC_EMP"),
						encuesta, companyCampaign, local, rs.getDate("FECHA_CREACION"),
						rs.getDate("FECHA_MODIFICACION"),estadoFormulario);			
				
				listaEncustasEmpresa.add(encuestaEmpresa);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return listaEncustasEmpresa;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<User> getUsers(List<Filter> filters,String operator)
	{
		List<User> userList = new ArrayList<User>();
		try {
			User user = null;
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT US.ID_USUARIO,US.USERNAME,US.PASSWORD,US.FIRSTNAME,US.TIPO, ");
			sql.append("US.LASTNAME,US.EMAIL,US.ENABLED,US.CREATED_ON,UR.ID_ROL,UR.AUTHORITY ");
			sql.append("FROM " + getSecondarySchema() + ".USUARIO US ");
			sql.append("INNER JOIN " + getSecondarySchema() + ".USUARIO_ROL UR ON UR.USUARIO_ID_USUARIO = US.ID_USUARIO ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY US.ID_USUARIO ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				user = new User();
				user.setId(rs.getLong("ID_USUARIO"));
				user.setUsername(rs.getString("USERNAME"));
				user.setFirstname(rs.getString("FIRSTNAME"));
				user.setLastname(rs.getString("LASTNAME"));
				user.setEmail(rs.getString("EMAIL"));
				user.setEnabled(rs.getBoolean("ENABLED"));
				user.setCreationDate(rs.getDate("CREATED_ON"));
				
				Role role = new Role(rs.getLong("ID_ROL"), rs.getString("AUTHORITY"));
				user.setRole(role);
				
				userList.add(user);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return userList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<ValidationType> getValidationTypes(List<Filter> filters,String operator)
	{
		List<ValidationType> validationTypeList = new ArrayList<ValidationType>();
		try {
			ValidationType validationType = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT VT.ID_TIPO_VALIDACION,VT.DESCRIPCION FROM " + getMainSchema() + ".VALIDACIONES_TIPOS VT");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY VT.ID_TIPO_VALIDACION ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				validationType = new ValidationType();
				validationType.setId(rs.getLong("ID_TIPO_VALIDACION"));
				validationType.setDescription(rs.getString("DESCRIPCION"));
				
				validationTypeList.add(validationType);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return validationTypeList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<Country> getCountries(List<Filter> filters,String operator)
	{
		List<Country> countryList = new ArrayList<Country>();
		try {
			Country country = null;
				conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT P.ID_PAIS,P.PAIS,P.CODIGO_ISO,P.ID_ZONA,P.NOMBRE_ZONA FROM " + getSecondarySchema() + ".PAIS P ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY P.PAIS ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				country = new Country();
				country.setId(rs.getString("ID_PAIS"));
				country.setCountry(rs.getString("PAIS"));
				country.setIsoCode(rs.getString("CODIGO_ISO"));
				country.setZoneId(rs.getLong("ID_ZONA"));
				country.setZoneName(rs.getString("NOMBRE_ZONA"));
				
				countryList.add(country);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return countryList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<MiningItem> getMiningItems(List<Filter> filters,String operator)
	{
		List<MiningItem> miningItemList = new ArrayList<MiningItem>();
		try {
			MiningItem miningItem = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("select MI.ID,MI.COD_CLANAE,MI.DESCRIPCION,MI.COD_CPC2,MI.COD_SENE,MI.NOMENCLADOR from " + getSecondarySchema() + ".MINERIA MI ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY MI.DESCRIPCION ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				miningItem = new MiningItem();
				miningItem.setId(rs.getLong("ID"));
				miningItem.setClanaeCode(rs.getString("COD_CLANAE"));
				miningItem.setCpc2Code(rs.getString("COD_CPC2"));
				miningItem.setDescription(rs.getString("DESCRIPCION"));
				miningItem.setNomenclative(rs.getString("NOMENCLADOR"));
				miningItem.setSeneCode(rs.getString("COD_SENE"));
				
				miningItemList.add(miningItem);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return miningItemList;
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public List<IndustrialProduct> getIndustrialProducts(List<Filter> filters,String operator)
	{
		List<IndustrialProduct> industrialProductList = new ArrayList<IndustrialProduct>();
		try {
			IndustrialProduct industrialProduct = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT CP.ID,CP.CODCLAPR,CP.DESCRIPCION,CP.COD_CPC2,CP.COD_SENE FROM  " + getSecondarySchema() + ".CLANAE_PRODUCTOS CP ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY CP.DESCRIPCION ");

			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				industrialProduct = new IndustrialProduct();
				industrialProduct.setId(rs.getLong("ID"));
				industrialProduct.setClanaeCode(rs.getString("CODCLAPR"));
				industrialProduct.setCpc2Code(rs.getString("COD_CPC2"));
				industrialProduct.setDescription(rs.getString("DESCRIPCION"));
				industrialProduct.setSeneCode(rs.getString("COD_SENE"));
				
				industrialProductList.add(industrialProduct);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return industrialProductList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
		
	public List<ValidationReason> getValidationReasons(List<Filter> filters,String operator)
	{
		List<ValidationReason> validationReasons = new ArrayList<ValidationReason>();
		try {
			ValidationReason validationReason = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("select VM.ID_VALIDACION_MOTIVO, VM.ID_VALIDACION,VM.DESCRIPCION,VM.OBSERVACION_OBLIGATORIA from " + getMainSchema() + ".VALIDACIONES_MOTIVOS VM");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY VM.ID_VALIDACION_MOTIVO ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				validationReason = new ValidationReason();
				
				validationReason.setId(rs.getLong("ID_VALIDACION_MOTIVO"));
				validationReason.setDescription(rs.getString("DESCRIPCION"));
				validationReason.setMandatoryObservation(rs.getBoolean("OBSERVACION_OBLIGATORIA"));
				
				validationReasons.add(validationReason);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return validationReasons;
	}	
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<Validation> getValidations(List<Filter> filters,String operator)
	{
		List<Validation> validationList = new ArrayList<Validation>();
		try {
			Validation validation = null;
			ValidationType validationType = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			Map<Long,ValidationType> validationTypesMap = new HashMap<Long, ValidationType>();
			sql.append("SELECT VAL.ID_VALIDACION,VAL.CODIGO,VAL.DESCRIPCION,VAL.ID_TIPO_VALIDACION FROM " + getMainSchema() + ".VALIDACIONES VAL");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY VAL.ID_VALIDACION ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				validation = new Validation();
				validation.setId(rs.getLong("ID_VALIDACION"));
				validation.setCode(rs.getString("CODIGO"));
				validation.setDescription(rs.getString("DESCRIPCION"));
				
				Long validationTypeID = rs.getLong("ID_TIPO_VALIDACION");
				
				validationType = validationTypesMap.get(validationTypeID);
				if(validationType == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "VT.ID_TIPO_VALIDACION", Filter.EQUAL_OPERATOR,validationTypeID,true);
					filters.add(filter);
					
					List<ValidationType> validationTypes = getValidationTypes(filters, ObjectDao.AND_OPERATOR);

					if(validationTypes != null && validationTypes.size() == 1)
					{
						validationType = validationTypes.get(0);
						validationTypesMap.put(validationTypeID, validationType);
					}
				}				
				validation.setValidationType(validationType);
				
				validationList.add(validation);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return validationList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<ValidationError> getValidationErrors(List<Filter> filters,String operator,String orderClause)
	{
		List<ValidationError> validationErrorList = new ArrayList<ValidationError>();
		try {
			ValidationError validationError = null;
			CompanyPoll companyPoll = null;
			Validation validation = null;
			ValidationReason validationReason = null;
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			Map<Long,CompanyPoll> companyPollsMap = new HashMap<Long,CompanyPoll>();
			Map<Long,Validation> validationsMap = new HashMap<Long,Validation>();
			Map<Long,ValidationReason> validationReasonsMap = new HashMap<Long,ValidationReason>();
			
			sql.append("SELECT VE.ID_VALIDACION_ERROR,VE.ID_ENC_EMP,VE.ID_VALIDACION,VE.OBSERVACIONES,VE.ID_VALIDACION_MOTIVO FROM " + getMainSchema() + ".VALIDACIONES_ERRORES VE");
			sql.append(" INNER JOIN " + getMainSchema() + ".VALIDACIONES V ON VE.ID_VALIDACION = V.ID_VALIDACION");
			sql.append(" INNER JOIN " + getMainSchema() + ".ENCUESTAS_EMPRESAS EE ON VE.ID_ENC_EMP = EE.ID_ENC_EMP ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ");
			if(orderClause != null)
			{
				sql.append(orderClause);	
			}			
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				validationError = new ValidationError();
				validationError.setId(rs.getLong("ID_VALIDACION_ERROR"));
				
				Long companyPollID = rs.getLong("ID_ENC_EMP");
				
				companyPoll = companyPollsMap.get(companyPollID);
				if(companyPoll == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_ENC_EMP", Filter.EQUAL_OPERATOR,companyPollID,true);
					filters.add(filter);
					
					List<CompanyPoll> companyPolls = getCompanyPolls(filters, ObjectDao.AND_OPERATOR);

					if(companyPolls != null && companyPolls.size() == 1)
					{
						companyPoll = companyPolls.get(0);
						companyPollsMap.put(companyPollID, companyPoll);
					}
				}				
				validationError.setCompanyPoll(companyPoll);
				
				Long validationID = rs.getLong("ID_VALIDACION");
				
				validation = validationsMap.get(validationID);
				if(validation == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "VAL.ID_VALIDACION", Filter.EQUAL_OPERATOR,validationID,true);
					filters.add(filter);
					
					List<Validation> validations = getValidations(filters, ObjectDao.AND_OPERATOR);

					if(validations != null && validations.size() == 1)
					{
						validation = validations.get(0);
						validationsMap.put(validationID, validation);
					}
				}				
				validationError.setValidation(validation);
				
				Long validationReasonID = rs.getLong("ID_VALIDACION_MOTIVO");
				
				if(validationReasonID != null)
				{
					validationReason = validationReasonsMap.get(validationReasonID);
					if(validationReason == null)
					{
						filters = new ArrayList<Filter>();
						Filter filter = new Filter(Filter.NUMERIC_FILTER, "VM.ID_VALIDACION_MOTIVO", Filter.EQUAL_OPERATOR,validationReasonID,true);
						filters.add(filter);
						
						List<ValidationReason> validationReasons = getValidationReasons(filters, ObjectDao.AND_OPERATOR);

						if(validationReasons != null && validationReasons.size() == 1)
						{
							validationReason = validationReasons.get(0);
							validationReasonsMap.put(validationReasonID, validationReason);
						}
					}				
					validationError.setValidationReason(validationReason);
				}				
				
				validationError.setObservations(rs.getString("OBSERVACIONES"));
				
				validationErrorList.add(validationError);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return validationErrorList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<FormMovement> getFormMovements(List<Filter> filters,String operator)
	{
		List<FormMovement> formMovementList = new ArrayList<FormMovement>();
		try {
			FormMovement formMovement = null;
			
			CompanyPoll companyPoll = null;
			User user= null;
			FormStatus formStatus = null;
			
			Map<Long, CompanyPoll> companyPollsMap = new HashMap<Long, CompanyPoll>();
			Map<Long, User> userMap = new HashMap<Long, User>();
			
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("select MF.ID_MOV_ENC,MF.ID_ENC_EMP,MF.ID_USUARIO,MF.ID_ESTADO_FORMULARIO,EF.ESTADO_FORMULARIO,MF.FECHA,MF.OBSERVACIONES,EE.ID_EMP_CAMP from " + 
						getMainSchema() + ".MOVIMIENTOS_FORMULARIOS MF " +
						"INNER JOIN " + getMainSchema() + ".ENCUESTAS_EMPRESAS EE ON MF.ID_ENC_EMP = EE.ID_ENC_EMP " + 
						"INNER JOIN " + getMainSchema() + ".ESTADOS_FORMULARIOS EF ON MF.ID_ESTADO_FORMULARIO = EF.ID_EST_FORM");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY MF.FECHA DESC ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				formMovement = new FormMovement();
				formMovement.setId(rs.getLong("ID_MOV_ENC"));
				
				Long companyPollID = rs.getLong("ID_ENC_EMP");
				companyPoll = companyPollsMap.get(companyPollID);
				if(companyPoll == null)
				{
					filters = new ArrayList<Filter>();
					//TODO
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "ENC_EMP.ID_ENC_EMP", Filter.EQUAL_OPERATOR,companyPollID,true);
					filters.add(filter);
					
					List<CompanyPoll> companyPolls = getCompanyPolls(filters, ObjectDao.AND_OPERATOR);

					if(companyPolls != null && companyPolls.size() == 1)
					{
						companyPoll = companyPolls.get(0);
						companyPollsMap.put(companyPollID, companyPoll);
					}
				}	
				formMovement.setCompanyPoll(companyPoll);
				
				Long userID = rs.getLong("ID_USUARIO");
				user = userMap.get(userID);
				if(user == null)
				{
					filters = new ArrayList<Filter>();
					//TODO
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "US.ID_USUARIO", Filter.EQUAL_OPERATOR,userID,true);
					filters.add(filter);
					
					List<User> users = getUsers(filters, ObjectDao.AND_OPERATOR);

					if(users != null && users.size() == 1)
					{
						user = users.get(0);
						userMap.put(userID, user);
					}
				}	
				formMovement.setUser(user);
				
				formStatus = new FormStatus(rs.getLong("ID_ESTADO_FORMULARIO"),rs.getString("ESTADO_FORMULARIO"));
				formMovement.setFormStatus(formStatus);
				formMovement.setDate(rs.getTimestamp("FECHA"));
				formMovement.setObservations(rs.getString("OBSERVACIONES"));
				formMovementList.add(formMovement);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return formMovementList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
		
	
	public List<PollStatus> getPollStatuses(List<Filter> filters,String operator)
	{
		List<PollStatus> pollStatusList = new ArrayList<PollStatus>();
		try {
			PollStatus pollStatus = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("select EST_ENC.ID_EST_ENC,EST_ENC.ESTADO_ENCUESTA from " + getMainSchema() + ".ESTADOS_ENCUESTAS EST_ENC");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY EST_ENC.ID_EST_ENC ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				pollStatus = new PollStatus();
				pollStatus.setId(rs.getLong("ID_EST_ENC"));
				pollStatus.setStatus(rs.getString("ESTADO_ENCUESTA"));
				
				pollStatusList.add(pollStatus);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return pollStatusList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	

	public List<Permission> getPermissions(List<Filter> filters,String operator)
	{
		List<Permission> permissionList = new ArrayList<Permission>();
		try {
			Permission permission = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT PER.ID_PERMISO,PER.PERMISO FROM " + getMainSchema() + ".PERMISOS PER ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY PER.ID_PERMISO ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				permission = new Permission();
				permission.setId(rs.getLong("ID_PERMISO"));
				permission.setDescription(rs.getString("PERMISO"));
				
				permissionList.add(permission);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		return permissionList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<Button> getButtons(List<Filter> filters,String operator)
	{
		List<Button> buttonList = new ArrayList<Button>();
		try {
			Button button = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT DISTINCT BOT.ID_BOTON,BOT.BOTON, BREE.ID_ROL, BREE.ID_EST_ENC FROM " + getMainSchema() + ".BOTONES BOT ");
			sql.append("INNER JOIN " + getMainSchema() + ".BOTONES_ROLES_ESTADOSENC BREE ON BREE.ID_BOTON = BOT.ID_BOTON ");
						
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY BOT.ID_BOTON ");
			
		//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				button = new Button();
				button.setId(rs.getLong("ID_BOTON"));
				button.setButton(rs.getString("BOTON"));
				
				buttonList.add(button);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return buttonList;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<UserCompanyCampaign> getUserCompanyCampains(List<Filter> filters,String operator)
	{		
		List<UserCompanyCampaign> userCompanyCampaignList = new ArrayList<UserCompanyCampaign>();
		try {
			UserCompanyCampaign userCompanyCampaign = null;
			User user = null;
			Permission permission = null;
			CompanyCampaign companyCampaign = null;
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			Map<Long, User> mapUsers = new HashMap<Long, User>();
			Map<Long, Permission> mapPermissions = new HashMap<Long, Permission>();
			Map<Long, CompanyCampaign> mapCompanyCampaign = new HashMap<Long, CompanyCampaign>();
						
			sql.append("select UEC.ID_USR_EMP_CAM,UEC.ID_USUARIO,UEC.ID_EMP_CAM,UEC.ID_PERMISO from " + getMainSchema() + ".USUARIOS_EMPRESAS_CAMPANAS UEC ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY UEC.ID_USR_EMP_CAM ");
			
		//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				userCompanyCampaign = new UserCompanyCampaign();
				userCompanyCampaign.setId(rs.getLong("ID_USR_EMP_CAM"));
				Long userID = rs.getLong("ID_USUARIO");
				user = mapUsers.get(userID);
				if(user == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "US.ID_USUARIO", Filter.EQUAL_OPERATOR,userID,true);
					filters.add(filter);
					
					List<User> users = getUsers(filters, ObjectDao.AND_OPERATOR);

					if(users != null && users.size() == 1)
					{
						user = users.get(0);
						mapUsers.put(userID, user);
					}
				}
				userCompanyCampaign.setUser(user);
				
				Long companyCampaignID = rs.getLong("ID_EMP_CAM");
				companyCampaign = mapCompanyCampaign.get(companyCampaignID);
				if(companyCampaign == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "EMP_CAM.ID_EMP_CAMP", Filter.EQUAL_OPERATOR,companyCampaignID,true);
					filters.add(filter);
					
					List<CompanyCampaign> companyCampaigns = getCompanyCampaigns(filters, ObjectDao.AND_OPERATOR);

					if(companyCampaigns != null && companyCampaigns.size() == 1)
					{
						companyCampaign = companyCampaigns.get(0);
						mapCompanyCampaign.put(companyCampaignID, companyCampaign);
					}
				}
				userCompanyCampaign.setCompanyCampaign(companyCampaign);
				
				Long permissionID = rs.getLong("ID_PERMISO");
				permission = mapPermissions.get(permissionID);
				if(permission == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "PER.ID_PERMISO", Filter.EQUAL_OPERATOR,permissionID,true);
					filters.add(filter);
					
					List<Permission> permissions = getPermissions(filters, ObjectDao.AND_OPERATOR);

					if(permissions != null && permissions.size() == 1)
					{
						permission = permissions.get(0);
						mapPermissions.put(permissionID, permission);
					}
				}
				userCompanyCampaign.setPermission(permission);
				userCompanyCampaignList.add(userCompanyCampaign);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return userCompanyCampaignList;
	}	
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<Campaign> getCampaigns(List<Filter> filters,String operator)
	{
		List<Campaign> campaignList = new ArrayList<Campaign>();
		try {
			Campaign campaign = null;
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT CAMP.ID_CAMPANA,CAMP.ANO,CAMP.DESCRIPCION FROM " + getMainSchema() + ".CAMPANAS CAMP ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY CAMP.ID_CAMPANA ");
			
		//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				campaign = new Campaign();
				campaign.setId(rs.getLong("ID_CAMPANA"));
				campaign.setYear(rs.getLong("ANO"));
				campaign.setDescription(rs.getString("DESCRIPCION"));
				
				campaignList.add(campaign);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return campaignList;
	}
	
	
	// Creado Por INDEC Trae estado de Empresa y cuit
	
	public CompanyCampaign getCompanyCampaignStatus(Long idEmpCamp)
	{
		CompanyCampaign estadoCuit = new CompanyCampaign();
		
		try {
		conn.abrirConexion();
		StringBuilder sql = new StringBuilder();
		sql.append("select  se.cuit , sc.descripcion , sec.ID_ESTADO_ENCUESTA from sirena.campanas sc ");
		sql.append("left join "+ getMainSchema() +".empresas_campanas sec on sec.id_campana = sc.id_campana ");
		sql.append("left join "+ getSecondarySchema() +".empresa se on sec.id_empresa =  se.id_empresa ");
		sql.append("where sec.id_emp_camp =" +idEmpCamp  );
		
	//	System.out.println(sql);
		List<BoundParam> parameters = new ArrayList<BoundParam>();
		ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
		
		while (rs.next()) {
		
			estadoCuit.setCompany(new Company());
			estadoCuit.getCompany().setCuit(rs.getString("CUIT"));
			
			estadoCuit.setCampaign(new Campaign());
			estadoCuit.getCampaign().setDescription(rs.getString("DESCRIPCION"));
			
			estadoCuit.setPollStatus( new PollStatus());
			estadoCuit.getPollStatus().setId(rs.getLong("ID_ESTADO_ENCUESTA"));
			
		}
		
		}catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}
		
		return estadoCuit;
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<CompanyCampaign> getCompanyCampaigns(List<Filter> filters,String operator)
	{
		List<CompanyCampaign> pollCampaignList = new ArrayList<CompanyCampaign>();
		try {
			CompanyCampaign pollCampaign = null;
			Campaign campaign = null;
			Company company = null;
			PollStatus pollStatus = null;
			
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			Map<Long, Company> mapCompanies = new HashMap<Long, Company>();
			Map<Long, PollStatus> mapPollStatus = new HashMap<Long, PollStatus>();
			Map<Long, Campaign> mapCampaigns = new HashMap<Long, Campaign>();
			
			sql.append("SELECT EMP_CAM.ID_EMP_CAMP,EMP_CAM.ID_EMPRESA,EMP_CAM.ID_ESTADO_ENCUESTA,EMP_CAM.ID_CAMPANA FROM " + getMainSchema() + ".EMPRESAS_CAMPANAS EMP_CAM ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY EMP_CAM.ID_EMP_CAMP ");
			
			//			System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				pollCampaign = new CompanyCampaign();
				pollCampaign.setId(rs.getLong("ID_EMP_CAMP"));
				
				Long companyID = rs.getLong("ID_EMPRESA");
				company = mapCompanies.get(companyID);
				if(company == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "EMP.ID_EMPRESA", Filter.EQUAL_OPERATOR,companyID,true);
					filters.add(filter);
					
					List<Company> companies = getCompanies(filters, ObjectDao.AND_OPERATOR);

					if(companies != null && companies.size() == 1)
					{
						company = companies.get(0);
						mapCompanies.put(companyID, company);
					}
				}
				pollCampaign.setCompany(company);
				
				campaign = mapCampaigns.get(rs.getLong("ID_CAMPANA"));
				if(campaign == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "CAMP.ID_CAMPANA", Filter.EQUAL_OPERATOR,rs.getLong("ID_CAMPANA"),true);
					filters.add(filter);
					
					List<Campaign> campaigns = getCampaigns(filters, ObjectDao.AND_OPERATOR);

					if(campaigns != null && campaigns.size() == 1)
					{
						campaign = campaigns.get(0);
						mapCampaigns.put(rs.getLong("ID_CAMPANA"), campaign);
					}
				}
				pollCampaign.setCampaign(campaign);
				
				pollStatus = mapPollStatus.get(rs.getLong("ID_ESTADO_ENCUESTA"));
				if(pollStatus == null)
				{
					filters = new ArrayList<Filter>();
					Filter filter = new Filter(Filter.NUMERIC_FILTER, "EST_ENC.ID_EST_ENC", Filter.EQUAL_OPERATOR,rs.getLong("ID_ESTADO_ENCUESTA"),true);
					filters.add(filter);
					
					List<PollStatus> pollStatuses = getPollStatuses(filters, ObjectDao.AND_OPERATOR);

					if(pollStatuses != null && pollStatuses.size() == 1)
					{
						pollStatus = pollStatuses.get(0);
						mapPollStatus.put(rs.getLong("ID_ESTADO_ENCUESTA"), pollStatus);
					}
				}
				pollCampaign.setPollStatus(pollStatus);
				
				pollCampaignList.add(pollCampaign);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return pollCampaignList;
	}
		
	
	//______________________________________________________________________________
	// Creado por la gente de INDEC y Re modificado Carga Inicial de Empresas Para ver por operador / empresa 
	
	
	public List<CompanyCampaign> getLogingCompanyCampaings(Long userID)
	{
		List<CompanyCampaign> pollCampaignList2 = new ArrayList<CompanyCampaign>();
		try {
			CompanyCampaign pollCampaign = null;
			Campaign campaign = null;
			Company company = null;
			PollStatus pollStatus = null;
			
			conn.abrirConexion();
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
							
			sql.append("SELECT EMP_CAM.ID_EMP_CAMP, EMP_CAM.ID_CAMPANA, SEMP.CUIT, SCAM.DESCRIPCION, PS.ESTADO_ENCUESTA ");
			sql.append("FROM "+ getSecondarySchema() +".EMPRESA SEMP LEFT JOIN " + getMainSchema() +".EMPRESAS_CAMPANAS EMP_CAM  ON SEMP.ID_EMPRESA = EMP_CAM.ID_EMPRESA ");
			sql.append("LEFT join " + getMainSchema()+ ".CAMPANAS SCAM on EMP_CAM.ID_CAMPANA = SCAM.ID_CAMPANA " );
			sql.append("LEFT join " + getMainSchema()+ ".ESTADOS_ENCUESTAS PS on EMP_CAM.ID_ESTADO_ENCUESTA = PS.ID_EST_ENC " );
			sql.append("WHERE EMP_CAM.ID_EMP_CAMP IN ( SELECT UEC.ID_EMP_CAM FROM "+ getMainSchema() +".USUARIOS_EMPRESAS_CAMPANAS UEC WHERE UEC.ID_USUARIO = " + userID +")");
			sql.append(" order by SEMP.CUIT asc");
					
		//				System.out.println(sql);
			
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
					pollCampaign = new CompanyCampaign();
					pollCampaign.setId(rs.getLong("ID_EMP_CAMP"));
			
						if(company == null)					
					{
					 pollCampaign.setCompany(new Company());
					 pollCampaign.getCompany().setCuit(rs.getString("CUIT"));				 
					}				
						if (campaign == null )					
					{
						pollCampaign.setCampaign(new Campaign());
						pollCampaign.getCampaign().setDescription(rs.getString("DESCRIPCION"));
					
					}
						if ( pollStatus == null )					
					{
						pollCampaign.setPollStatus(new PollStatus());
						pollCampaign.getPollStatus().setStatus(rs.getString("ESTADO_ENCUESTA"));
					}
						pollCampaignList2.add(pollCampaign);
			}
							
				
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return pollCampaignList2;
	}
	
		
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	private String getFilterQuery(Filter filter)
	{
		if (filter.getSpecialFilter() != null && filter.getSpecialFilter().length() > 0)
		{
			return filter.getSpecialFilter();
		}
		else
		{
			StringBuilder query = new StringBuilder();
			query.append(filter.getAttribute());
			query.append(" ");
			query.append(filter.getOperator());
			query.append("?");
						
			return query.toString();			
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void updateFormStatus(Long pollCompanyID, Long status,Long userID,String comments) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		List<BoundParam> parametros = null;
		try {
			conn.abrirConexion();	
//			conn.startTransaction();
			parametros = new ArrayList<BoundParam>();
			parametros.add(new BoundParam(1, status,java.sql.Types.NUMERIC));
			parametros.add(new BoundParam(2, pollCompanyID,java.sql.Types.NUMERIC));
			conn.executePreparedStatement("UPDATE " + getMainSchema() + ".ENCUESTAS_EMPRESAS SET ID_ESTADO_FORMULARIO = ?, FECHA_MODIFICACION = sysdate where ID_ENC_EMP = ?", parametros);
			String queryStr = null;
			if (comments != null)
			{
				parametros = new ArrayList<BoundParam>();
				parametros.add(new BoundParam(1, pollCompanyID,java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(2, userID,java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(3, status,java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(4, comments,java.sql.Types.VARCHAR));
				queryStr = "INSERT INTO " + getMainSchema() + ".MOVIMIENTOS_FORMULARIOS(ID_MOV_ENC,ID_ENC_EMP,ID_USUARIO,ID_ESTADO_FORMULARIO,OBSERVACIONES,FECHA)";
						queryStr+= " VALUES (SEQ_MOV_ENC.NEXTVAL,?,?,?,?,sysdate)";
			}
			else
			{
				parametros = new ArrayList<BoundParam>();
				parametros.add(new BoundParam(1, pollCompanyID,java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(2, userID,java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(3, status,java.sql.Types.NUMERIC));
				queryStr = "INSERT INTO " + getMainSchema() + ".MOVIMIENTOS_FORMULARIOS(ID_MOV_ENC,ID_ENC_EMP,ID_USUARIO,ID_ESTADO_FORMULARIO,FECHA)";
						queryStr+= " VALUES (SEQ_MOV_ENC.NEXTVAL,?,?,?,sysdate)";
			}
			conn.executePreparedStatement(queryStr, parametros);
			
			conn.commitTransaction();
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") +  " '"
							+ e.getMessage() + "'");
		}
		finally
		{
			conn.cerrarConexion();
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void updateFormStatusByCompanyCampaign(Long companyCampaignID, Long statusID, Conexion conn) throws SQLException
	{
		List<BoundParam> parametros = null;
		parametros = new ArrayList<BoundParam>();
		parametros.add(new BoundParam(1, statusID,java.sql.Types.NUMERIC));
		parametros.add(new BoundParam(2, companyCampaignID,java.sql.Types.NUMERIC));
		conn.executePreparedStatement("UPDATE " + getMainSchema() + ".ENCUESTAS_EMPRESAS SET ID_ESTADO_FORMULARIO = ? WHERE ID_EMP_CAMP = ?", parametros);
	}
	
	public void updateFormStatusByCompanyCampaign(Long companyCampaignID, Long statusID,String clause, Conexion conn) throws SQLException
	{
		List<BoundParam> parametros = null;
		parametros = new ArrayList<BoundParam>();
		parametros.add(new BoundParam(1, statusID,java.sql.Types.NUMERIC));
		parametros.add(new BoundParam(2, companyCampaignID,java.sql.Types.NUMERIC));
		conn.executePreparedStatement("UPDATE " + getMainSchema() + ".ENCUESTAS_EMPRESAS SET ID_ESTADO_FORMULARIO = ? WHERE ID_EMP_CAMP = ? AND ID_ENC_EMP " + clause, parametros);
	}
	
	public void updateFormStatusByCompanyCampaign(Long companyCampaignID, Long statusID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		List<BoundParam> parametros = null;
		try {
			conn.abrirConexion();
			parametros = new ArrayList<BoundParam>();
			parametros.add(new BoundParam(1, statusID,java.sql.Types.NUMERIC));
			parametros.add(new BoundParam(2, companyCampaignID,java.sql.Types.NUMERIC));
			conn.startTransaction();
			conn.executePreparedStatement("UPDATE " + getMainSchema() + ".ENCUESTAS_EMPRESAS SET ID_ESTADO_FORMULARIO = ? WHERE ID_EMP_CAMP = ?", parametros);
//			String queryStr = null;
//			
//				parametros = new ArrayList<BoundParam>();
//				parametros.add(new BoundParam(1, pollCompanyID,java.sql.Types.NUMERIC));
//				parametros.add(new BoundParam(2, userID,java.sql.Types.NUMERIC));
//				parametros.add(new BoundParam(3, statusID,java.sql.Types.NUMERIC));
//				queryStr = "INSERT INTO MOVIMIENTOS_FORMULARIOS(ID_MOV_ENC,ID_ENC_EMP,ID_USUARIO,ID_ESTADO_FORMULARIO,FECHA)";
//						queryStr+= " VALUES (SEQ_MOV_ENC.NEXTVAL,?,?,?,sysdate)";
//			
//			conn.executePreparedStatement(queryStr, parametros);
			
			conn.commitTransaction();
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") +  " '"
							+ e.getMessage() + "'");
		}
		finally
		{
			conn.cerrarConexion();
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public List<String> getEmailsToSendNotifications(Long companyCampaignID)
	{
		List<String> results = new ArrayList<String>();
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, companyCampaignID,java.sql.Types.NUMERIC));
		String sql = "SELECT EMAIL "
				+ "FROM " + getMainSchema() + ".EMPRESAS_CAMPANAS ec "
				+ "JOIN " + getMainSchema() + ".USUARIOS_EMPRESAS_CAMPANAS uec on ec.ID_EMP_CAMP = uec.ID_EMP_CAM "
				+ "join " + getSecondarySchema() + ".USUARIO u on uec.id_usuario = u.id_usuario "
				+ "join " + getSecondarySchema() + ".USUARIO_ROL ur on u.id_usuario = ur.USUARIO_ID_USUARIO "
				+ "join " + getMainSchema() + ".NOTIFICACIONES_ESTADOS_ROLES ner on ur.AUTHORITY = ner.ID_ROL and ec.ID_ESTADO_ENCUESTA = ner.ID_EST_ENC "
				+ "WHERE EC.ID_EMP_CAMP = ? and u.EMAIL like '%@%' ";		
		ResultSet rs = null;
		try {
			conn.abrirConexion();
			rs = conn.getPreparedStatementResultSet(sql,parameters);
			while (rs.next()) 
			{
				results.add(rs.getString(1));
			}
			return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally 
		{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn.cerrarConexion();
		}
	}	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void savePollValues(List<PollValue> pollValues,Long userID,Long formStatus) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		List<BoundParam> parametros = null;
		try {
			conn.abrirConexion();			
			if(pollValues != null && pollValues.size() > 0)
			{
				Long encuestaEmpresaID = pollValues.get(0).getCompanyPoll().getId();	
				parametros = new ArrayList<BoundParam>();
				parametros.add(new BoundParam(1, formStatus,java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(2, encuestaEmpresaID,java.sql.Types.NUMERIC));
				conn.executePreparedStatement("UPDATE " + getMainSchema() + ".ENCUESTAS_EMPRESAS SET ID_ESTADO_FORMULARIO = ? where ID_ENC_EMP = ?", parametros);
				
				Long pollStatusID = pollValues.get(0).getCompanyPoll().getCompanyCampaign().getPollStatus().getId();
				Long companyCampaignID = pollValues.get(0).getCompanyPoll().getCompanyCampaign().getId();
			
				if(pollStatusID == PollService.POLL_STATUS_NEW || pollStatusID == PollService.POLL_STATUS_REJECTED)
				{
					parametros = new ArrayList<BoundParam>();
					parametros.add(new BoundParam(1, PollService.POLL_STATUS_EDITED,java.sql.Types.NUMERIC));
					parametros.add(new BoundParam(2, companyCampaignID,java.sql.Types.NUMERIC));
					conn.executePreparedStatement("UPDATE " + getMainSchema() + ".EMPRESAS_CAMPANAS SET ID_ESTADO_ENCUESTA = ? where ID_EMP_CAMP = ?", parametros);	
				}
			}
			
			for(PollValue pollValue : pollValues)
			{
				parametros = new ArrayList<BoundParam>();
				parametros.add(new BoundParam(1, pollValue.getValue(),java.sql.Types.VARCHAR));
				parametros.add(new BoundParam(2, pollValue.getId(),java.sql.Types.NUMERIC));
				conn.executePreparedStatement("UPDATE " + getMainSchema() + ".ENCUESTAS_EMPRESAS_VALORES SET VALOR = ? WHERE ID_ENC_DEF_EMP = ?", parametros);
								
				parametros = new ArrayList<BoundParam>();
				parametros.add(new BoundParam(1, pollValue.getCompanyPoll().getId(),java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(2, pollValue.getPollDefinition().getId(),java.sql.Types.NUMERIC));
				parametros.add(new BoundParam(3, pollValue.getValue(),java.sql.Types.VARCHAR));
				String queryStr = "INSERT INTO " + getMainSchema() + ".ENCUESTAS_EMPRESAS_VALORES_LOG(ID_ENC_DEF_EMP,ID_ENC_EMP,ID_ENC_DEF,VALOR,FECHA)";
						queryStr+= " VALUES (SEQ_LOG_POLLS_DEF_CMP.NEXTVAL,?,?,?,sysdate)";
						
				conn.executePreparedStatement(queryStr, parametros);
			}
			conn.commitTransaction();
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") +  " '"
							+ e.getMessage() + "'");
		}
		finally
		{
			conn.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void execSUM(Long companyCampaignID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();
			conn.execSumaLocales(companyCampaignID);
			
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.SUMerror") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void execSUMVariables(Long companyCampaignID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();
			conn.execSumaVariables(companyCampaignID);
			
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.SUMerror") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void updatePollStatus(Long companyCampaignID,Long pollStatusID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();
			conn.execUpdatePollStatus(companyCampaignID, pollStatusID);
			
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void updatePollAndFormStatus(Long companyCampaignID,Long pollStatusID,Long formStatusID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();	
			conn.startTransaction();
			
			conn.execUpdatePollStatus(companyCampaignID, pollStatusID);
			updateFormStatusByCompanyCampaign(companyCampaignID, formStatusID,conn);			
			
			conn.commitTransaction();
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public void updatePollAndFormStatus(Long companyCampaignID,Long pollStatusID,Long formStatusID,Conexion conn) throws ExDBException, SQLException
	{
		conn.execUpdatePollStatus(companyCampaignID, pollStatusID);
		updateFormStatusByCompanyCampaign(companyCampaignID, formStatusID,conn);			
			
	}
	
	public void validatePolls(Long companyCampaignID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();			
			conn.execValidatePolls(companyCampaignID);
			String companyPollsClause = getCompanyPollsClauseForValidate(companyCampaignID);
			
			if(companyPollsClause == null)
			{
				updateFormStatusByCompanyCampaign(companyCampaignID, PollService.FORM_STATUS_VALIDATED_OK,conn);	
			}
			else
			{
				String firstClause = "IN " + companyPollsClause;
				String secondClause = "NOT IN " + companyPollsClause;
				updateFormStatusByCompanyCampaign(companyCampaignID, PollService.FORM_STATUS_VALIDATED_ERROR,firstClause,conn);
				updateFormStatusByCompanyCampaign(companyCampaignID, PollService.FORM_STATUS_VALIDATED_OK,secondClause,conn);
			}
			conn.commitTransaction();
		} catch (SQLException e) {
			try {
				e.printStackTrace();
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
	}
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	public SendValidationResult sendPolls(Long companyCampaignID) throws ExDBException
	{
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();
			conn.execValidatePolls(companyCampaignID);
			int errors = getInvalidValidationErrorsLegnth(companyCampaignID);
			int warnings = getInvalidValidationWarningsWithoutCommentsLegnth(companyCampaignID);
			boolean valid = false;
			
			if(errors == 0 && warnings == 0)
			{
				updatePollAndFormStatus(companyCampaignID,PollService.POLL_STATUS_PENDING_REVIEW,PollService.FORM_STATUS_PENDING_REVIEW,conn);
				valid = true;
			}
			SendValidationResult result = new SendValidationResult(errors, warnings, valid);
			conn.commitTransaction();
			return result;
		} catch (SQLException e) {
			try {
				e.printStackTrace();
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public String getCompanyPollsClauseForValidate(Long companyCampaignID) throws SQLException
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, companyCampaignID,java.sql.Types.NUMERIC));
		String sql = "SELECT  DISTINCT VE.ID_ENC_EMP FROM " + getMainSchema() + ".VALIDACIONES_ERRORES VE " +
					"INNER JOIN " + getMainSchema() + ".VALIDACIONES V ON V.ID_VALIDACION = VE.ID_VALIDACION " +
					"INNER JOIN " + getMainSchema() + ".ENCUESTAS_EMPRESAS EE ON EE.ID_ENC_EMP = VE.ID_ENC_EMP WHERE EE.ID_EMP_CAMP = ? AND ((V.ID_TIPO_VALIDACION = 1 AND VE.OBSERVACIONES IS NULL) " +
					"OR (V.ID_TIPO_VALIDACION = 2))";
		ResultSet rs = null;
		StringBuilder strBuild = new StringBuilder();
		strBuild.append("(");
		try {
			rs = conn.getPreparedStatementResultSet(sql,parameters);
			while (rs.next()) 
			{
				strBuild.append(rs.getString(1));
				strBuild.append(",");			
			}
			strBuild.append(")");
			if(strBuild.length() == 2)
			{
				return null;
			}
			else
			{
				return strBuild.toString().replace(",)", ")");
			}			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally 
		{
			rs.close();
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public int getInvalidValidationWarningsWithoutCommentsLegnth(Long companyCampaignID)
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, companyCampaignID,java.sql.Types.NUMERIC));
		String sql = "SELECT COUNT(*) FROM " + getMainSchema() + ".VALIDACIONES_ERRORES VE " +
        "INNER JOIN " + getMainSchema() + ".VALIDACIONES V ON V.ID_VALIDACION = VE.ID_VALIDACION " +
        "INNER JOIN " + getMainSchema() + ".ENCUESTAS_EMPRESAS EE ON EE.ID_ENC_EMP = VE.ID_ENC_EMP "+
        "LEFT JOIN " + getMainSchema() + ".VALIDACIONES_MOTIVOS VM ON VM.ID_VALIDACION = V.ID_VALIDACION and VM.ID_VALIDACION_MOTIVO = VE.ID_VALIDACION_MOTIVO " + 
        "WHERE EE.ID_EMP_CAMP = ? AND ((V.ID_TIPO_VALIDACION = 1 AND VE.ID_VALIDACION_MOTIVO IS NULL) OR (V.ID_TIPO_VALIDACION = 1 AND OBSERVACIONES IS NULL AND OBSERVACION_OBLIGATORIA = 1)) ";
		ResultSet rs;
		try {
			int results = 0;
			rs = conn.getPreparedStatementResultSet(sql,parameters);
			if(rs.next()) 
			{
				results = rs.getInt(1);
				return results;
			}
			return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public int getInvalidValidationErrorsLegnth(Long companyCampaignID)
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, companyCampaignID,java.sql.Types.NUMERIC));
		String sql = "SELECT COUNT(*) FROM " + getMainSchema() + ".VALIDACIONES_ERRORES VE " +
					"INNER JOIN " + getMainSchema() + ".VALIDACIONES V ON V.ID_VALIDACION = VE.ID_VALIDACION " +
					"INNER JOIN " + getMainSchema() + ".ENCUESTAS_EMPRESAS EE ON EE.ID_ENC_EMP = VE.ID_ENC_EMP WHERE EE.ID_EMP_CAMP = ? AND V.ID_TIPO_VALIDACION = 2 ";
		ResultSet rs;
		try {
			int results = 0;
			rs = conn.getPreparedStatementResultSet(sql,parameters);
			if(rs.next()) 
			{
				results = rs.getInt(1);
				return results;
			}
			return results;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
		
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public SendValidationResult validateSendValidation(Long companyCampaignID)
	{
		SendValidationResult sendValidationResult = new SendValidationResult();
		List<Filter> filters = new ArrayList<Filter>();
		
		//Traigo las validaciones tipo Warning sin comentarios
		Filter filter = new Filter(Filter.STRING_FILTER, "VE.ID_ENC_EMP", Filter.EQUAL_OPERATOR,companyCampaignID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "V.ID_TIPO_VALIDACION", Filter.EQUAL_OPERATOR,ValidationService.WARNING_TYPE_ID,true);
		filters.add(filter);
		//Filtro que observaciones tenga algun texto
		filter = new Filter(" (observaciones is null or length(trim(observaciones)) is null)");
		filters.add(filter);
		List<ValidationError> warningValidations = getValidationErrors(filters, AND_OPERATOR,null);
		if(warningValidations != null)
		{
			sendValidationResult.setWarningsCount(warningValidations.size());
		}
		else
		{
			sendValidationResult.setWarningsCount(0);
		}
		//Traigo las validaciones tipo ERROR
		filters = new ArrayList<Filter>();		
		filter = new Filter(Filter.STRING_FILTER, "VE.ID_ENC_EMP", Filter.EQUAL_OPERATOR,companyCampaignID,true);
		filters.add(filter);
		filter = new Filter(Filter.NUMERIC_FILTER, "V.ID_TIPO_VALIDACION", Filter.EQUAL_OPERATOR,ValidationService.ERROR_TYPE_ID,true);
		filters.add(filter);
		List<ValidationError> errorValidations = getValidationErrors(filters, AND_OPERATOR,null);
		if(errorValidations != null)
		{
			sendValidationResult.setErrorsCount(errorValidations.size());
		}
		else
		{
			sendValidationResult.setErrorsCount(0);
		}
		//Si no tengo validaciones incorrectas(Errores o Advertencias sin comentarios) seteo valid en true, si no pongo false
		if(sendValidationResult.getErrorsCount() == 0 && sendValidationResult.getWarningsCount() == 0)
		{
			sendValidationResult.setValid(true);
		}
		else
		{
			sendValidationResult.setValid(false);
		}		
		return sendValidationResult;
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public void updateValidationError(Long validationErrorID,Long reasonID, String observation) throws ExDBException {
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();	
			
			List<BoundParam> parametros = null;
			parametros = new ArrayList<BoundParam>();
			parametros.add(new BoundParam(1, reasonID,java.sql.Types.NUMERIC));
			parametros.add(new BoundParam(2, observation,java.sql.Types.VARCHAR));
			parametros.add(new BoundParam(3, validationErrorID,java.sql.Types.NUMERIC));
			conn.executePreparedStatement("UPDATE " + getMainSchema() + ".VALIDACIONES_ERRORES SET ID_VALIDACION_MOTIVO = ?, OBSERVACIONES = ? WHERE ID_VALIDACION_ERROR = ?", parametros);
			
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.save.error") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
		
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public void updateRejectComment(Long formMovementID,Long userID,String observation) throws ExDBException {
		Properties msge = Utils.getProperties("resource/messages_es.properties");
		try {
			conn.abrirConexion();	
			
			List<BoundParam> parametros = null;
			parametros = new ArrayList<BoundParam>();
			parametros.add(new BoundParam(1, userID,java.sql.Types.NUMERIC));
			parametros.add(new BoundParam(2, observation,java.sql.Types.VARCHAR));
			parametros.add(new BoundParam(3, formMovementID,java.sql.Types.NUMERIC));
			
			conn.executePreparedStatement("UPDATE " + getMainSchema() + ".MOVIMIENTOS_FORMULARIOS SET FECHA = SYSDATE,ID_USUARIO = ?, OBSERVACIONES = ? WHERE ID_MOV_ENC = ?", parametros);
			
		} catch (SQLException e) {
			try {
				conn.rollbackTransaction();
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ExDBException(
					msge.getProperty("savePoll.rejectComment.error") + " '"
							+ e.getMessage() + "'");
		}
		finally{
			conn.cerrarConexion();
		}
		
	}
	
	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public void processForm(Workbook wb, Long companyCampaignID, Long pollID,Long localID, Statement stmt, Statement stmt_up) throws SQLException
	{
		ResultSet rs = null;
		String valorS = null;
		double valorN;
		
		String queryStr = null;
		if(localID == null || localID == 0)
		{
			queryStr = "(SELECT ID_ENC_EMP FROM ENCUESTAS_EMPRESAS WHERE ID_ENCUESTA = " + pollID + " AND ID_EMP_CAMP = " + companyCampaignID + " AND ID_LOCAL IS NULL)";
		}
		else
		{
			queryStr = "(SELECT ID_ENC_EMP FROM ENCUESTAS_EMPRESAS WHERE ID_ENCUESTA = " + pollID + " AND ID_EMP_CAMP = " + companyCampaignID + " AND ID_LOCAL =" + localID + ")";
		}
		
		rs = stmt.executeQuery("SELECT * FROM ENCUESTAS_DEFINICION where ID_ENCUESTA = " + pollID + " order by hoja,fila,columna");
		
		while (rs.next())
		{			
			try
			{
				if(rs.getInt("HOJA") == 1 && rs.getInt("FILA") == 12 && rs.getInt("COLUMNA") == 9)
				{
				//	System.out.println("");
				}
				
				valorS = wb.getSheetAt(rs.getInt("HOJA") - 1).getRow(rs.getInt("FILA") - 1).getCell(rs.getInt("COLUMNA") - 1).getStringCellValue();
			
				if (valorS.trim().length() > 0)
				{
					stmt_up.executeUpdate("update ENCUESTAS_EMPRESAS_VALORES set VALOR = '"+ valorS.trim() + "' where id_enc_emp = "
							+ queryStr +" and id_enc_def = "+rs.getInt("ID_ENC_DEF"));
				}
			}
			catch (NullPointerException e){}
			catch (IllegalStateException e){
				try {
					valorN = wb.getSheetAt(rs.getInt("HOJA") - 1).getRow(rs.getInt("FILA") - 1).getCell(rs.getInt("COLUMNA") - 1).getNumericCellValue();
					stmt_up.executeUpdate("update ENCUESTAS_EMPRESAS_VALORES set VALOR = '"+ valorN + "' where id_enc_emp = "+ queryStr +" and id_enc_def = "+rs.getInt("ID_ENC_DEF"));	
				} 
				catch (IllegalStateException e2){}
                catch (NumberFormatException e2){}				
			}
		}
	}

	
	//______________________________________________________________________________
	// Creado por la gente de Cordoba
	
	
	public boolean checkIsStartRevision(Long companyCampaignID)
	{
		List<BoundParam> parameters = null;
		parameters = new ArrayList<BoundParam>();
		parameters.add(new BoundParam(1, companyCampaignID,java.sql.Types.NUMERIC));
		parameters.add(new BoundParam(2, PollService.FORM_STATUS_PENDING_REVIEW,java.sql.Types.NUMERIC));
		
		String sql = "select count(*) from " + getMainSchema() + ".ENCUESTAS_EMPRESAS where ID_EMP_CAMP = ? and NOT ID_ESTADO_FORMULARIO = ? ";		
		ResultSet rs = null;
		try {
			conn.abrirConexion();
			rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			if(rs.next())
			{
				int count = rs.getInt(1);
				if(count == 1)
				{
					return true;
				}
				else
				{
					return false;
				}	
			}
			return false;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		finally 
		{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn.cerrarConexion();
		}
	}

	
	//______________________________________________________________________________
	// Creado por la gente de INDEC
	
	public List<IndustrialMP> getIndustrialMP(List<Filter> filters,String operator)
	{
		List<IndustrialMP> industrialMPList = new ArrayList<IndustrialMP>();
		try {
			IndustrialMP industrialMP = null;
			conn.abrirConexion();	
			StringBuilder sql = new StringBuilder();
			List<BoundParam> parameters = new ArrayList<BoundParam>();
			
			sql.append("SELECT CP.ID,CP.CODCLA,CP.DESCRIPCION,CP.COD_CPC2,CP.COD_SENE FROM  " + getSecondarySchema() + ".CLANAE_MATERIAPRIMA CP ");
			
			if(filters != null && filters.size() > 0)
			{
				boolean whereUsed = false;
				int i = 0;
				int countParameters = 0;
				for(Filter myFilter : filters)
				{
					i++;
					if(myFilter.getType() == Filter.SPECIAL_FILTER)
					{
						sql.append(myFilter.getSpecialFilter());						
					}
					else
					{
						if (!whereUsed)
						{
							sql.append(" WHERE ");
							whereUsed = true;
						}
						sql.append(getFilterQuery(myFilter));
						if(i < filters.size())
						{
							sql.append(" ");
							sql.append(operator);
							sql.append(" ");
						}
					}
					if(myFilter.isParameter())
					{
						countParameters++;
						parameters.add(new BoundParam(countParameters, myFilter.getValue(),getDataTypeValue(myFilter.getType())));	
					}
				}
			}
			sql.append(" ORDER BY CP.DESCRIPCION ");

			//System.out.println(sql);
			ResultSet rs = conn.getPreparedStatementResultSet(sql,parameters);
			
			while (rs.next()) {
				
				industrialMP = new IndustrialMP();
				industrialMP.setId(rs.getLong("ID"));
				industrialMP.setClanaeCode(rs.getString("CODCLA"));
				industrialMP.setCpc2Code(rs.getString("COD_CPC2"));
				industrialMP.setDescription(rs.getString("DESCRIPCION"));
				industrialMP.setSeneCode(rs.getString("COD_SENE"));
				
				industrialMPList.add(industrialMP);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			conn.cerrarConexion();
		}	
		return industrialMPList;
	}

}
